VBA voor smarties: Afhankelijke invoerlijsten (comboboxen)

Afhankelijke invoerlijsten (comboboxen)

Met afhankelijke invoerlijsten (comboboxen) kun je een gebruiker begeleiden bij selectie van gegevens uit een database.
Je kunt ervoor kiezen de van elkaar afhankelijke gegevens vooraf te definiëren.
Je kunt ook VBA het werk laten doen en uit een database afhankelijke invoerlijsten laten samenstellen.
Die koppel je dan aan een volgende combobox.
Comboboxen kunnen voorkomen als ActiveX-control in een werkblad of als combobox in een Userform.
De code daarvoor is verschillend en komt daarom apart aan bod. Met VBA kun je op vele manieren afhankelijke invoerlijsten maken; de code hieronder gebruikt twee lussen.
Onder Methode II staan 2 bestanden die hetzelfde resultaat bewerkstelligen met compactere VBA-code.

1.1 Comboboxen in een werkblad

In het voorbeeldbestand staan de 'database'gegevens staan in het werkblad 'database'.
Werkblad 'output' bevat de comboboxen keus1, keus2 en keus.
Het nummer in de naam van de combobox komt overeen met de kolom van de database: zo blijft de VBA-code eenvoudig.

Bij het openen van het werkboek:
- zet de 'database' in array sn
- combobox 'keus1' in werkblad 'output' krijgt een invoerlijst met unieke waarden uit kolom A van de database.
- comboboxen keus2 en keus3 worden geleegd.

Private Sub Workbook_Open()

sn = Sheets("database").Cells(1).CurrentRegion
For j = 1 To UBound(sn)

If InStr(c01 & ",", "," & sn(j, 1) & ",") = 0 Then c01 = c01 & "," & sn(j, 1)

Next

With Sheets("output")

.keus1.List = Split(Mid(c01, 2), ",")
.keus2.Clear
.keus3.Clear
.Range("B5:B7").ClearContents

End With

End Sub

Bij de wijziging van een combobox:
De produktie van een afhankelijke invoerlijst vindt plaats als de waarde van een combobox wijzigt; de '_change'-gebeurtenis.
De comboboboxen kunnen met hun objectnamen (keus1, keus2 en keus3) benaderd worden omdat de code in de VBA-module van het werkblad met de comboboxen staat.
Omdat iedere keuze analoge code gebruikt voor de produktie van invoerlijsten is deze ondergebracht in de funktie 'lijst'.
De 'change'-gebeurtenis van een combobox roept de funktie 'lijst' aan.

- controleer of de combobox, waarvan de invoerlijst afhankelijk is, een waarde bevat
- doorloop de database in array sn en kontroleer per record of die overeenkomt met de ingevoerde gegevens in de comboboxen
- als ze overeenkomen voeg dan de waarde uit de -met de kolom van de afhankelijke combobox overeenkomende- kolom van de database toe aan de tekstreeks c01, als die waarde daarin nog niet voorkomt.
- zet de tot een 1-dimensionele array geconverteerde tekstreeks in de combobox.

Private Sub keus1_Change()

Range("B5:B7").ClearContents
keus2.ListIndex = -1
keus3.ListIndex = -1

If keus1.ListIndex > -1 Then keus2.List = Split(lijst(1), ",")

End Sub

Private Sub keus2_Change()

If keus2.ListIndex > -1 Then keus3.List = Split(lijst(2), ",")

End Sub

Function lijst(x)

sn = Sheets("database").Cells(1).CurrentRegion

For j = 1 To UBound(sn)

For jj = 1 To x

If sn(j, jj) <> Sheets("output").OLEObjects("keus" & jj).Object.Value Then Exit For

Next

If jj = x + 1 And InStr(c01 & ",", "," & sn(j, jj) & ",") = 0 Then c01 = c01 & "," & sn(j, jj)

Next

lijst = Mid(c01, 2)

End Function

1.2 Comboboxen in een werkblad: methode II

Dit bestand realiseert hetzelfde met compactere VBA-code.

Methode II sheet

2.1 Comboboxen in een Userform

De gegevens staan in het werkblad 'database'.
Het userform 'scherm' bevat de comboboxen keus1, keus2 en keus3.
Het nummer in de naam van de combobox komt overeen met de kolom van de database: zo blijft de VBA-code eenvoudig.

Bij het initialiseren van het Userform:
- declareer de array sn als een private variabele
- zet de 'database' in array sn
- combobox 'keus1' krijgt een invoerlijst met unieke waarden uit kolom A van de database.

Dim sn

Private Sub UserForm_Initialize()

sn = Sheets("database").Cells(1).CurrentRegion
For j = 1 To UBound(sn)

If InStr(c01 & ",", "," & sn(j, 1) & ",") = 0 Then c01 = c01 & "," & sn(j, 1)

Next

keus1.List = Split(Mid(c01, 2), ",")

End Sub

Bij de wijziging van een combobox:
De produktie van een afhankelijke invoerlijst vindt plaats als de waarde van een combobox wijzigt; de '_change'-gebeurtenis.
De comboboboxen kunnen met hun objectnamen (keus1, keus2 en keus3) benaderd worden omdat de code in de VBA-module van het userform staat.
Omdat iedere keuze analoge code gebruikt voor de produktie van invoerlijsten is deze ondergebracht in de funktie 'lijst'.
De 'change'-gebeurtenis van een combobox roept de funktie 'lijst' aan.

- controle of de combobox, waarvan de invoerlijst afhankelijk is, een waarde bevat
- doorloop de database in array sn en kontroleer per record of die overeenkomt met de ingevoerde comboboxen
- als ze overeenkomen voeg dan de waarde uit de -met de kolom van de afhankelijke combobox overeenkomende- kolom van de database toe aan de tekstreeks c01, als die waarde daarin nog niet voorkomt.
- zet de tot een 1-dimensionele array geconverteerde tekstreeks in de volgende combobox.

Private Sub keus1_Change()

keus2.ListIndex = -1 keus3.ListIndex = -1 If keus1.ListIndex > -1 Then keus2.List = Split(lijst(1), ",")

End Sub

Private Sub keus2_Change()

If keus2.ListIndex > -1 Then keus3.List = Split(lijst(2), ",")

End Sub

Function lijst(x)

For j = 1 To UBound(sn)

For jj = 1 To x

If sn(j, jj) <> Me("keus" & jj).Value Then Exit For

Next
If jj = x + 1 And InStr(c01 & ",", "," & sn(j, jj) & ",") = 0 Then c01 = c01 & "," & sn(j, jj)

Next

lijst = Mid(c01, 2)

End Function

2.2 Methode II voor Userform comboboxen

Dit bestand realiseert hetzelfde met compactere VBA-code.

Methode II userform