create combo box depending on results from previous combo box on VBA

[VBAmalta89]

So I have a dropdown menu in a user form which contains a list of options taken from an excel worksheet. Now the user will select one option. Each option is related to another set of lists in a worksheet.

What I want to do is when the user picks example option A a particular set of options are visible in the next drop down box (combo box), but if he selects option B in the first combo box, then the set of options in the second combo box are different and taken from another list.

How can I achieve this using VBA as i am new to this thing?

Thanks

 

[MickG]

Try this:-

Private [COLOR=navy]Sub[/COLOR] ComboBox1_Change()
[COLOR=navy]Select[/COLOR] [COLOR=navy]Case[/COLOR] ComboBox1.value
    [COLOR=navy]Case[/COLOR] [COLOR=navy]Is[/COLOR] = "B": ComboBox2.RowSource = "B1:B10"
    [COLOR=navy]Case[/COLOR] [COLOR=navy]Is[/COLOR] = "C": ComboBox2.RowSource = "C1:C10"
    [COLOR=navy]Case[/COLOR] [COLOR=navy]Is[/COLOR] = "D": ComboBox2.RowSource = "D1:D10"
[COLOR=navy]End[/COLOR] Select
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[DauntingGecko]

There are different ways you can do it.

Your best bet would be to type your different lists in a spreadsheet and name them as different ranges. E.G. ComboA, ComboB, ComboC, etc

Then when someone chooses "A" from the drop down list, code it so it pulls out values from each cell in your named range ComboA.

Done.

 

[VBAmalta89]

how do i change the reference to a particular sheet and range of cells, i.e for example sheet called drop down menu and cell range B2:B8?

 

[VBAmalta89]

i understand what u are telling me but how do I code this? I am rather new to the concept of VBA so Im still trying to grasp the principles

 

[MickG]

First In column "A1: A4" write the letteres A to E.
Secondly Name a single column range in another sheet "MyRng"
Now open the vb Editer with your Userform and comboboxes:-
named "Combobox1" and "Combobox2" showing in the Editer.
Right Click "Combobox1 and select "properties" .
The properties window on the left hand side will show, scroll down to "Rowsource" and enter:- A1:A4
Return to Userform and Double click Combobox1 and it will open at the "Combobox1.Change" Event
Paste the code below into the Module, Make sure you don't have 2 lines with "Private Sub ComboBox1_Change().
Close the editer, Now when you run the code, and select "D"" it will select the named range on the other sheet, called "MyRng".

Private Sub ComboBox1_Change()
Select Case ComboBox1.value
    Case Is = "A": ComboBox2.RowSource = "B1:B10"
    Case Is = "B": ComboBox2.RowSource = "C1:C10"
    Case Is = "C": ComboBox2.RowSource = "D1:D10"
    Case Is = "D": ComboBox2.RowSource = "MyRng"
End Select
End Sub