Formulier maken in Excel, met aan elkaar gerelateerde keuzelijsten
merel
behulp van 3 keuzelijsten. Ik heb behalve het formulier een tweede blad met
data.
Bijvoorbeeld;
Op het datablad staan staan 3 kolommen met gegevens; Bedrijf, Afdeling en
Medewerker. Ieder bedrijf heeft meerdere afdelingen, iedere afdeling heeft
meerdere medewerkers. Er bestaat dus een soort boomstructuur.
Als de gebruiker in het formulier, in veld 1 kiest voor bedrijf A, moet hij
in veld 2 kunnen kiezen uit de afdelingen van bedrijf A.
Vervolgens moet hij in veld 3 kunnen kiezen uit een lijst medewerkers van de
gekozen afdeling.
Ik weet dat ik d.m.v. gegevensvalidatie een keuzelijst met mogelijke waarden
maken.
Het probleem is echter dat , als ik gegevens validatie gebruik, in veld 3
een lijst krijg van alle mogelijke medewerkers en niet van de medewerkers van
bedrijf A, afdeling 1.
De lijsten zijn erg lang, waardoor mijn formulier gebruiksonvriendelijk is.
Bovendien hebben de gebruikers nu de mogelijkheid om een combinatie te
kiezen die niet mogelijk is. (bijvoorbeeld combinatie bedrijf/afdeling die
niet mogelijk is omdat de betreffende afdeling niet bestaat bij het
betreffende bedrijf)
Kan iemand mij uitleggen hoe ik dit moet aanpakken?
( Ik heb geen ervaring met visual basic of macro's)
Jan B.
dit is een ingewikkeld probleem, maar niet onmogelijk.
Het is in principe mogelijk om de validatie van de tweede cel te laten
bepalen door de keuze in de eerste cel.
bijvoorbeeld:
Data > Valideren
Cel D5: Toestaan: lijst, Bron: A1:A6
Cel E5: Toestaan: lijst, Bron: =ALS(D5<>"";B1:B6)
dan krijg je het volgende . . . . :
als cel D5 leeg is, dan kun je in E5 geen selectie maken
als C5 iets bevat, dan verschijnt in E5 de lijst B1:B6
echter:
wil je een grote lijst hierin verwerken dan, je ziet het al, worden de
formules in de validaties te complex en te lang.
De oplossing moet dus worden gezocht in Vbasic. De gegevens bedrijf,
afdeling en medewerker moeten een zuivere databasestructuur hebben, zoals:
bedrijf afdeling medewerker
a 1 jan
a 2 piet
a 2 els
b 1 frits
daar is mee te werken in vbasic. Maar een validatielijst hiermee maken is
een ramp want dan zou je bedrijf a je 3x in de valdatielijst zien staan.
Oplossing in Vbasic:
Je maakt in het blad met behulp van de werkbalk 'formulieren' drie
"keuzelijsten met invoervak" voor respectievelijk bedrijf,afdeling en
mederwerker. Geef de keuzelijsten ook die namen en koppel ze aan een cel
(sleep met Alt ingedrukt)
Elke keuzelijst koppel je aan een macro, een subroutine die de inhoud van de
volgende keuzelijst verzorgt.
deze routines moeten het volgende doen:
1. de 1e subroutine maakt een lijst maakt met unieke bedrijven (elk bedrijf
wordt maar 1x genoemd) , sorteert die lijst en vult er de eerste keuzelijst
mee.
De subroutine moet worden opgestart als het werkblad wordt geactiveerd.
( Private Sub Worksheet_Activate() ).
2. de 2e subroutine maakt een unieke, gesorteerde lijst van de afdelingen
die bij het gekozen bedrijf horen en kopieert die lijst naar de tweede
keuzelijst.
De subroutine wordt door de eerste keuzelijst geactiveerd.
3. de 3e subroutine maakt een unieke, gesorteerde lijst van de medewerkers
die bij het gekozen bedrijf EN de gekozen afdeling horen en kopieert deze
lijst naar de derde keuzelijst.
de eerste keuzelijst zorgt ervoor dat eerste subroutine 2 en vervolgens
subroutine 3 wordt geactiveerd,
de tweede keuzelijst zorgt ervoor dat subroutine 2 en vervolgens subroutine
3 wordt geactiveerd.
Daar merk je niks van.
Wellicht is er iemand in je omgeving die dit kan realiseren of misschien kun
je het zélf. Lukt dat niet dan wil ik het wel voor je maken. Ik heb dan
echter wel het bestand nodig en een beetje tijd.
--
met vriendelijke groet,
Jan B.
"merel" schreef:
merel
Ik heb het zelf geprobeerd, maar voor mij is het toch net iets te
ingewikkeld.
Naar aanleiding van jouw reactie ben ik op zoek gegaan naar iemand die met
VBA kan werken.
Ook kan ik daardoor nu beter uitleggen wat er gemaakt moet worden in het
formulier.
Hartelijk bedankt
Hanno
Maak een formulier met de naam UserForm1
Plaats in het formulier 3 comboboxen met de namen: cboxBedrijven,
cboxAfdelingen, cboxMedewerkers.
Plaats ook een knop met de naam commandbutton1
Plaats onderstaande script in de module van het formulier.
Probeer het maar.
Private Sub UserForm_Initialize()
Dim cell As Range
Dim item
Dim Bedrijven As New Collection
cboxAfdelingen.Enabled = False
cboxMedewerkers.Enabled = False
'Bedrijven inlezen
On Error Resume Next
For Each cell In Range(Cells(1, 1), Cells(65536, 1).End(xlUp))
Bedrijven.Add cell.Value, CStr(cell.Value)
Next cell
On Error GoTo 0
For Each item In Bedrijven
UserForm1.cboxBedrijven.AddItem item
Next item
End Sub
Private Sub cboxBedrijven_Change()
cboxAfdelingen.Enabled = (cboxBedrijven.Value <> "")
Dim Afdelingen As New Collection
Dim cell As Range
Dim item
On Error Resume Next
For Each cell In Range(Cells(1, 2), Cells(65536, 2).End(xlUp))
If cell.Offset(0, -1).Value = cboxBedrijven.Value Then
Afdelingen.Add cell.Value, CStr(cell.Value)
End If
Next cell
On Error GoTo 0
cboxAfdelingen.Clear
For Each item In Afdelingen
UserForm1.cboxAfdelingen.AddItem item
Next item
End Sub
Private Sub cboxAfdelingen_Change()
cboxMedewerkers.Enabled = (cboxAfdelingen.Value <> "")
Dim Medewerkers As New Collection
Dim cell As Range
Dim item
On Error Resume Next
For Each cell In Range(Cells(1, 3), Cells(65536, 3).End(xlUp))
If (cell.Offset(0, -1).Value = cboxAfdelingen.Value) And _
(cell.Offset(0, -2).Value = cboxBedrijven.Value) Then
Medewerkers.Add cell.Value, CStr(cell.Value)
End If
Next cell
On Error GoTo 0
cboxMedewerkers.Clear
For Each item In Medewerkers
UserForm1.cboxMedewerkers.AddItem item
Next item
End Sub
Private Sub CommandButton1_Click()
MsgBox "Bedrijf: " & cboxBedrijven & Chr(10) & _
"Afdeling: " & cboxAfdelingen & Chr(10) & _
"Medewerker: " & cboxMedewerkers
End Sub
merel
Ik ga het meteen proberen. Alleen nog een vraagje....
Moet ik in VBA kiezen voor het invoegen van een userform?
Ik heb namelijk mijn formulier gemaakt op een werkblad van Excel.
Behalve die drie keuzelijsten staat er ook nog andere info op het formulier.
Bovendien moet de info van het formulier ook nog vertaald worden naar
barcodes om tenslotte te kunnen uitprinten, met kop en voettekst. Ik weet
niet hoe ik dit moet doen op een userform van VBA.
Kan ik ook de combobozen invoegen op mijn formullier op het Excel werkblad
of werkt de macro dan niet?
bedankt merel
"Hanno" schreef:
Hanno
Wanneer je de keuzelijsten op het werkblad zelf zet doe je het
volgende:
Op en de VBA-editor
In het linkerdeel (projectverkenner) dubbelklik je op de module van
het werkblad dat je wilt gebruiken bv Blad1(Blad1)
Plak in het rechterdeel dat dan wordt geopend deze twee subroutines
Private Sub cboxBedrijven_Change()
cboxAfdelingen.Enabled = (cboxBedrijven.Value <> "")
Dim Afdelingen As New Collection
Dim cell As Range
Dim item
On Error Resume Next
For Each cell In Range(Cells(1, 2), Cells(65536, 2).End(xlUp))
If cell.Offset(0, -1).Value = cboxBedrijven.Value Then
Afdelingen.Add cell.Value, CStr(cell.Value)
End If
Next cell
On Error GoTo 0
cboxAfdelingen.Clear
For Each item In Afdelingen
ActiveSheet.cboxAfdelingen.AddItem item
Next item
End Sub
Private Sub cboxAfdelingen_Change()
cboxMedewerkers.Enabled = (cboxAfdelingen.Value <> "")
Dim Medewerkers As New Collection
Dim cell As Range
Dim item
On Error Resume Next
For Each cell In Range(Cells(1, 3), Cells(65536, 3).End(xlUp))
If (cell.Offset(0, -1).Value = cboxAfdelingen.Value) And _
(cell.Offset(0, -2).Value = cboxBedrijven.Value) Then
Medewerkers.Add cell.Value, CStr(cell.Value)
End If
Next cell
On Error GoTo 0
cboxMedewerkers.Clear
For Each item In Medewerkers
ActiveSheet.cboxMedewerkers.AddItem item
Next item
End Sub
Dubbelklik in de projectverkenner op de module ThisWorkbook
Plak in de module de volgende subroutine:
Private Sub Workbook_Open()
Dim cell As Range
Dim item
Dim Bedrijven As New Collection
'Indien je een ander bladnaam hebt dan Blad1, deze wijzigen.
Blad1.cboxAfdelingen.Enabled = False
Blad1.cboxMedewerkers.Enabled = False
'Bedrijven inlezen
On Error Resume Next
For Each cell In Range(Cells(1, 1), Cells(65536, 1).End(xlUp))
Bedrijven.Add cell.Value, CStr(cell.Value)
Next cell
On Error GoTo 0
For Each item In Bedrijven
Blad1.cboxBedrijven.AddItem item
Next item
End Sub
De keuzelijsten heten in de subroutines respectievelijk:
cboxBedrijven, cboxAfdelingen, cboxMedewerkers
Wijzig deze namen in je keuzelijsten of gebruik je eigennamen en
wijzig ze dan overeenkomstig met de namen in de subroutines hierboven.
Om de waardes uit de keuzelijsten in een cel te plaatsen, stel je de
eigenschap Linkedcell
in op bijvoorbeeld: Blad1!F1 Waarbij natuurlijk Blad! de naam van
het blad is en F1 de desbetreffende cel.
Mocht je het nu niet echt lukken mail me dan je werkmap, dan plaats ik
ze er voor je in.
Succes!
Hanno
PcWinkeler
ik ben zelf hier ook mee bezig.
Is er een mogelijkheid dat je mij zegt hoe je data eruit ziet.
Bij mij heb ik een afdeling, daarin programma's en onder programma's heb ik
tracks.
ook een boomstructuur.
Maakt het niet uit hoe de data erin staat?
Peter Winkeler.
"Hanno" schreef:
pollice...@gmail.com
Het is mij gelukt om onderstaande formule in te voeren;
=ALS(D5<>"";B1:B6)
Op welke manier kan ik deze formule uitbreiden als er bijvoorbeeld meerdere keuze's zijn op D5 die elk een andere keuzelijst moeten voortbrengen?
Alvast bedankt
Daniele