Dependent Drop Down lists (multiple drop down)

[jijy]

Hi,

I am a newbie to VBA.

I am trying to create dependant dropdown lists based on four different fields.
and am currently trying to make it so that I can have a list of options in one drop down list, and depending on what gets picked there, make it so that the
options for the second drop down list correspond with the first. I found vb code (by Matt) on one of the forums but couldn't make it work.

The screen shot of the excel file and the code are attached. I know it is full of mistakes. Hope someone can help me.

Private Sub CommandButton1_Click() 
Dim myLastRow As Integer 
 
Application.ScreenUpdating = False 
 
Range("A2").Select 
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _ 
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 
 
ComboBox1.Clear 
ComboBox2.Clear 
myLastRow = Range("a65000").End(xlUp).Row 
Range("A2").Select 
 
Do Until ActiveCell.Row > myLastRow 
    If ActiveCell.Value <> ActiveCell.Offset(-1, 0).Value Then 
        ComboBox1.AddItem ActiveCell.Value 
    End If 
    ActiveCell.Offset(1, 0).Select 
Loop 
Application.ScreenUpdating = False 
 
End Sub 
 
Private Sub ComboBox1_Change() 
Dim myLastRow As Integer 
 
Application.ScreenUpdating = False 
 
Range("A2").Select 
Selection.Sort Key1:=Range("A2"), Key2:=Range("B2"), Order1:=xlAscending, Order2:=xlAscending, Header:=xlYes 
ComboBox2.Clear 
myLastRow = Range("a65000").End(xlUp).Row 
Range("A2").Select 
Do Until ActiveCell.Value = ComboBox1.Value 
    ActiveCell.Offset(1, 0).Select 
Loop 
 
Do Until ActiveCell.Value <> ComboBox1.Value 
    If ActiveCell.Offset(0, 1).Value <> ActiveCell.Offset(-1, 1).Value _ 
        Or ActiveCell.Offset(0, 0).Value <> ActiveCell.Offset(-1, 0).Value Then 
        ComboBox2.AddItem ActiveCell.Offset(0, 1).Value 
    End If 
    ActiveCell.Offset(1, 0).Select 
Loop 
Application.ScreenUpdating = False 
 
 
End Sub 
Private Sub ComboBox2_Change() 
Dim myLastRow As Integer 
 
Application.ScreenUpdating = False 
 
Range("A2").Select 
Selection.Sort Key1:=Range("A2"), Key2:=Range("B2"), Key3:=Range("C2"), Order1:=xlAscending, Order2:=xlAscending, Header:=xlYes 
ComboBox3.Clear 
myLastRow = Range("a65000").End(xlUp).Row 
Range("A2").Select 
Do Until ActiveCell.Value = ComboBox2.Value 
    ActiveCell.Offset(1, 0).Select 
Loop 
 
Do Until ActiveCell.Value <> ComboBox2.Value 
    If ActiveCell.Offset(0, 1).Value <> ActiveCell.Offset(-1, 1).Value _ 
        Or ActiveCell.Offset(0, 0).Value <> ActiveCell.Offset(-1, 0).Value Then 
        ComboBox2.AddItem ActiveCell.Offset(0, 1).Value 
    End If 
    ActiveCell.Offset(1, 0).Select 
Loop 
Application.ScreenUpdating = False 
 
 
End Sub 
Private Sub ComboBox3_Change() 
 
End Sub 
 
Private Sub ComboBox4_Change() 
 
End Sub  

[rylo]

Hi

Can't see the screen shot, but perhaps you could attach a sample file that has data to match what you are trying to do. Include the code you have given, and give a scenario - say if you pick xxx from combobox1, then you would see yyy,zzzz,aaa in combobox2......


rylo

[jijy]

Hi Rilo,

I am attaching the screen shot. I have 4 drop downs. To illustrate, if I select Fruit in drop down and Apple in drop down 2, I should only have two items in dropdown 3 (Golden & Natural), once I select one from dropdown 3, I should only have "Ontario" as available option in dropdown 4.

This is what I want to achieve.

Hope you can help.

Thanks

Jijy

[rylo]

Hi

See if the attached file gets you started.

rylo

[jijy]

Hi Rylo,

Thanks a lot for your help. Really appreciate the time & effort you took to help out.

I am going to modify this a little bit and will come back to you if I have questions.

Thanks once again.

Jijy