Conditional Drop-Down boxes

[Nate Westcott]

I am wondering if it is possible to write a bit of VBA that would show different drop-down menus based on certain index values selected from other drop-down menus. For instance, in one drop-down, I can select "Fruit" or "Meat". If I select "Fruit", another drop-down menu appears, which offers selections like "Pear", "Orange", and Banana".

Is this possible? Thank you for any help anyone can offer!
Nate

[GeneralDisarray]

EDIT - found a mistake. Be Right Back.

[GeneralDisarray]

For an example: I used this to allow for State / County selection on a project. This design would work for any situation where your first selection list can be viewed as the 'header row' for a table that list all the options (see attached sheet labeled 'HIDDEN' for my full table).

Both list, require data validation be used. Also, I put the 'heavy lifting' into the name manager in two named formulas:

First list: States of the United States, formula is =HIDDEN!$A$1:INDEX(HIDDEN!$1:$1,COUNTA(HIDDEN!$1:$1))

Second list: to provide Counties for a given state is given by: =INDEX(HIDDEN!$A:$XFD,2,MATCH(VISIBLE!$C$3,HIDDEN!$1:$1,0)):INDEX(HIDDEN!$A:$XFD,COUNTA(INDEX(HIDDEN!$A:$XFD,0,MATCH(VISIBLE!$C$3,HIDDEN!$1:$1,0))),MATCH(VISIBLE!$C$3,HIDDEN!$1:$1,0))


EDIT:
If you want the second value to go away when the first value is changed, you need VBA. Attached is a second example that does this (using the following VBA in the Sheet2 ('VISIBLE') module.

Option Explicit




Private Sub Worksheet_Change(ByVal Target As Range)

    Dim YES_NO As Integer
    Dim possibleCounties As Range, found As Range

    On Error GoTo PROC_ERR
    Application.EnableEvents = False
    
    'State Changed - Clear County Entry IF that county is not part of the new state's list'
    If Not Intersect(Target, Sheet2.Range("C3")) Is Nothing Then
        
    'Check State Name'
        Set found = Sheet1.Range("1:1").Find(WHAT:=Target.Value, LookIn:=xlValues, Lookat:=xlWhole)
        If found Is Nothing Then
            MsgBox ("State Name invalid.  Use pick-list if unsure of spelling." & Chr(13) & Chr(13) & "     Entry, " & Sheet1.Range("B10").Value & ", not found in state list.")
            Sheet2.Range("C3").ClearContents
            Sheet2.Range("C5").ClearContents
            Application.EnableEvents = True
            Exit Sub
            
        ElseIf found = "" Then
            Sheet2.Range("C5").ClearContents
            Application.EnableEvents = True
            Exit Sub
        End If
        
        If found Is Nothing Then
            Sheet2.Range("C5").ClearContents
        Else
            'State was found - check check county.'
                Set found = found.EntireColumn.Find(WHAT:=Sheet2.Range("C5").Value, LookIn:=xlValues, Lookat:=xlWhole)
                If found Is Nothing Then Sheet2.Range("C5").ClearContents
        End If
    
    End If '
    
    
    
PROC_ERR:
        Application.EnableEvents = True
        If Err.Description <> "" Then MsgBox ("ERROR:  " & Err.Description)

End Sub

[scottc_00]

One other thing I should mention.
You should probably use an event procedure to default to the first item in the list of the new Category selected.
For instance if you choose Meat and Beef, and then change the category to Fruits you dont want Beef to stay.
So create an event procedure on a change to the first dropdown to change the second dropdown to the first item.

If you need help on event procedures you can probably find many many post describing how to do it on here in the VBA section.

Good Luck

[scottc_00]

I actually take a different approach when doing this
I use Data validation as dropdowns

Dropdown 1 (in cell f2) I would select from a list Meat, Fruits, Vegatables
Dropdown 2 (in cell F3) has the list of each selection in dropdown 1.

Dropdown 2 refers to a range (using data validation list) for example A1:A20
The list of meats may be in B1:B20 Beef, Chicken, Fish, Pork, etc
Fruits in C1:C20 Pear, Apple, Grapes, etc
Vegetables in D1:D20 Corn, Lettuce, etc
in cell A1 use the formula if($F$2 = "Meat",B1, if($F$2="Fruits",C1 etc
in cell A2 use the formula if($F$2 = "Meat",B2, if($F$2="Fruits",C2 etc

Hope this helps
Let me know if you need more details

[FDibbins]

Attached is a non-VBA way to do this, using dependant drop-downs. It uses named ranges for each category and sub-category, and then uses INDIRECT() to find the right range