Help with drop lists in a userform!

Hi,
I would like to know if this is possible: I have got 2 drop lists in a Userform, and when you select one value in the first drop list it will show only certain values in the next drop list: for example, in my first drop list there are two categories football and rugby, so if I select rugby it will select only for my second drop list just the names of the rugby players and if I select football just football players name.

Is is possible?

Hi,

Yes, it is feasible ...
Take a look at an excellent explanation from Debra
http://www.contextures.com/xlDataVal02.html

HTH
Carim

Thank you this is perfect within an excel shhet but:
Is is possible to do it in a userform ?

Try This:

Make a UserForm with two ComboBoxes called ComboBox1 & ComboBox2 and paste the code below in the UserForm code.

Cheers

Tom


Private Sub ComboBox1_Change()

Select Case ComboBox1.ListIndex

Case 0 'FRUIT
ComboBox2.Clear 'Clear Existing Values
ComboBox2.AddItem "Apple" 'ListIndex = 0
ComboBox2.AddItem "Banana" 'ListIndex = 1
ComboBox2.AddItem "Orange" 'ListIndex = 2

'Use Drop Down List
ComboBox2.Style = fmStyleDropDownList
'Combobox values are ListIndex values
ComboBox2.BoundColumn = 0
'Set Combobox to first entry
ComboBox2.ListIndex = 0

Case 1 'VEG
ComboBox2.Clear 'Clear Existing Values
ComboBox2.AddItem "Potato" 'ListIndex = 0
ComboBox2.AddItem "Carrot" 'ListIndex = 1
ComboBox2.AddItem "Bean" 'ListIndex = 2

'Use Drop Down List
ComboBox2.Style = fmStyleDropDownList
'Combobox values are ListIndex values
ComboBox2.BoundColumn = 0
'Set Combobox to first entry
ComboBox2.ListIndex = 0

Case 2 ' Meat
ComboBox2.Clear 'Clear Existing Values
ComboBox2.AddItem "Beef" 'ListIndex = 0
ComboBox2.AddItem "Chicken" 'ListIndex = 1
ComboBox2.AddItem "Lamb" 'ListIndex = 2

'Use Drop Down List
ComboBox2.Style = fmStyleDropDownList
'Combobox values are ListIndex values
ComboBox2.BoundColumn = 0
'Set Combobox to first entry
ComboBox2.ListIndex = 0

End Select

End Sub

Private Sub UserForm_Initialize()

'Add Values to ComboBox1.
ComboBox2.Clear 'Clear Existing Values
ComboBox1.AddItem "FRUIT" 'ListIndex = 0
ComboBox1.AddItem "VEG" 'ListIndex = 1
ComboBox1.AddItem "MEAT" 'ListIndex = 2

'Use Drop Down List
ComboBox1.Style = fmStyleDropDownList
'Combobox values are ListIndex values
ComboBox1.BoundColumn = 0
'Set Combobox to first entry
ComboBox1.ListIndex = 0

End Sub

Here is the example spreadsheet

your example works very well, thank you for your help!!!!

Hi,
following this long macro, I had a problem: basically I have one userforn with these two drop lists and a button "enter" which normally sends the value of the cell into a sheet, but when it does this the value of the second drop list in my sheet is equal to 0,1 or 2 and not beef chiken, lamb or potatoe... like it must be with the example.


How can I solve that?

Thank you

Here you go. This works.

You have solved my problem: in fact I was saying to transfer the data from the userform to the sheet Combox2.Value and in fact It was ComboBox2.Text


Thank you again