woensdag 15 oktober 2014

Excel: datavalidatie en getrapte keuze

In Excel kun je een cel valideren via de tab Gegevens =>gegevensvalidatie.

We krijgen dan dit dialoogvenster:


Bij Toestaan kunnen we dan kiezen voor Lijst en vervolgens bij Bron voor het bereik waarin de data staan.

Bij een getrapte keuze kiezen we bijvoorbeeld in een eerste cel het werelddeel en in de cel ernaast willen we dan alleen landen zien die bij het betreffende werelddeel horen.

In het onderstaande voorbeeld geven we de data die we in ons voorbeeld gaan gebruiken, van A1:E6:

afrika amerika australië azië europa
nigeria brazilië nieuw zeeland china nederland
boeroendi argentinië australië japan belgië
liberia equador nieuw guinea thailand polen
sierra leone peru pakistan duitsland
mauretanië bolivia india engeland

We gaan hier namen gebruiken om de getrapte keuze te laten werken.

We selecteren A1:E6. We kiezen dan de tab Formules en vervolgens Maken o.b.v. selectie. We krijgen dan:


Hierin vinken we alleen het bovenste vakje aan. Dan klikken we op OK. We hebben dan vijf namen gemaakt:

afrika
amerika
australië
azië
europa

Vervolgens maken we nog de naam Werelddelen gebaseerd op het bereik A1:E1.

Cel H1 van hetzelfde blad valideren we vervolgens met de formule:

=werelddelen


In de naastliggende cel I1 doen we hetzelfde maar dan met de formule:

=INDIRECT(H1)


Vanaf dit moment zal de gemaakte keuze in cel H1 de geboden lijst in I1 bepalen.


We blijven nog met één probleem zitten: in cel I1 zou niet de oude keus moeten blijven staan als we in H1 een ander werelddeel kiezen. Daarvoor hebben we VBA nodig.

Achter ons blad plaatsen we dan de code:

Private Sub Worksheet_Change(ByVal Target As Range)
    'MsgBox Target.Address
    If Target.Address = Range("H1").Address Then
        Range("I1").Value = ""
        Range("I1").Select
    End If
End Sub

Het is ook mogelijk met VBA de vervolglijst in cel I1 automatisch open te klappen.

Via +Ron Kanij heb ik de volgende code gekregen:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
    On Error GoTo foutje: 
    If ActiveCell.Validation.Type > 0 Then Application.SendKeys ("%{down}") 

    Exit Sub 
foutje: 
End Sub


Het bestand Excelgetraptekeuze.xlsm is te downloaden via:

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

Verdere tips: je kunt het bestand exceltips.pdf (laatst gewijzigd (12-9-2014) hier downloaden:



Een reactie posten