VBA voor smarties: Afhankelijke validatie

Afhankelijke validatie

Afhankelijke validatie is een handig hulpmiddel om een gebruiker te begeleiden bij selectie van records uit een database.
Zonder VBA kun je geen validatielijsten maken van gegevens in een ander werkblad, tenzij je gebruik maakt van benoemde bereiken.
Om die benoemde bereiken aan te passen aan de invoer zul je dynamische bereiken moeten defini‰ren.
Excel heeft daar geen waterdichte methoden voor.
Als je in de validatielijsten alleen unieke gegevens wil hebben wordt dit tamelijk complex.
Met VBA kan dat eenvoudiger.
Met VBA kun je op vele manieren afhankelijke validatie realiseren; de code hieronder gebruikt twee lussen.

Toelichting op de code

De gegevens staan in het werkblad 'database'.
In werkblad 'output' hebben de cellen A2, B2 en C2 een lege validatielijst gekregen.
De VBA-code hoeft daarom de lijst per cel alleen maar aan te passen (modify).
De kolom van iedere cel met een validatielijst komt overeen met een kolom van de database: zo blijft de VBA-code eenvoudig.

Bij het openen van het werkboek:
- zet de code de 'database' in array sn
- krijgt in werkblad 'output' cel A2 een lijst met unieke waarden uit kolom A van de database.
- krijgen de cellen B2 en C2 een lege validatielijst
- worden cellen B2,C2 en B5:B7 leeg gemaakt

Private Sub Workbook_Open()

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

For j = 2 To UBound(sn)

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

Next

With Sheets("output")

.Range("A2").Validation.Modify 3, 1, 1, Mid(c01, 2)
.Range("B2").Validation.Modify 3, 1, 1, ","
.Range("C2").Validation.Modify 3, 1, 1, ","
.Range("B2:C2,B5:B7").ClearContents

End With

End Sub

De produktie van een validatielijst vindt plaats als een van de cellen met een afhankelijke validatielijst aktief wordt:
- controle of de aktieve cel een afhankelijke validatielijst moet hebben
- controle of de cel, waarvan de validatielijst afhankelijk moet zijn, gevuld is
- doorloop de database en kontroleer per record of die overeenkomt met de ingevoerde gegevens in rij 2
- als ze overeenkomen voeg dan de waarde uit de -met de kolom van de aktieve cel- overeenkomende kolom van de database toe aan de tekstreeks c01, als die waarde daarin nog niet voorkomt.
- wijs de tekstreeks c01 toe als validatielijst; de komma zorgt ervoor dat de tekstreeks een lijst wordt.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Select Case Target.Address
Case "$B$2", "$C$2"

If Target.Offset(, -1) = "" Then Exit Sub

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

For jj = 1 To Target.Column - 1

If sn(j, jj) <> Cells(2, jj) Then Exit For

Next

If jj = Target.Column And InStr(c01 & ",", "," & sn(j, jj) & ",") = 0 Then c01 = c01 & "," & sn(j, jj)

Next
Target.Validation.Modify 3, 1, 1, Mid(c01, 2)

End Select

End Sub

De code in de gebeurtenisprocedure Worksheet_Change speelt geen rol bij de produktie van de validatielijsten.

Methode II

Ter illustratie kun je dit bestand downloaden.

Methode II

Daarin wordt hetzelfde gerealiseerd met wat minder VBA code.