Expert: Bob Umlas - 3/28/2008

Question
Hi, Bob. Excel 2003. Some time ago, you explained how to get a drop down validation to be dependant on another dropdown validation using INDIRECT. However, I now want to know if this is possible in a userform. I want one dropdown to be department (no problem with this), but then I want a second one to just show a list of sections within the relevant department in the first dropdown. Hope you can give me a pointer or direct me to a link on the web. Regards  Graham

Answer
Suppose in A1:A2 you have
Fruit
Vegetable.
The Userform Initialization code would use:
Private Sub UserForm_Initialize()
   Me.ComboBox1.List = Application.Transpose(Range("A1:A2"))
End Sub

Now you also have ranges named Fruit and Vegetable, so when the user selects an item from the first combobox:
Private Sub ComboBox1_Change()
   Me.ComboBox2.List = Application.Transpose(Range(Me.ComboBox1.Value))
End Sub

HTH