Dependent Drop Down lists (multiple drop down)

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.

PHP Code: 

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 


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

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

Hi

See if the attached file gets you started.

rylo

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