Select case for dependant lists

[Lynda]

A few days ago I posted a question on dependant lists using combo boxes from
the FORMS toolbar. It appears as though I will have to use the combo box from
the control toolbox because as hard as I try I can’t seem to get the FORMS
combo box to work. FSt1 did his/her best to help me and suggested it might be
worth using a select case. I have never heard of this before so I would be
grateful for some help using this function. What I have is a survey that
includes 2 drop down lists in combo boxes. The first list has approximately
10 categories and in the second list there are about 10-15 sub categories per
category. I want people to be able to choose a category from the first list
and then the second list will bring up the appropriate sub categories. I
would appreciate any assistance as this is driving me insane.

 

[Dave Peterson]

I put a couple of dropdowns from the Forms toolbar on Sheet1.

They were called "Drop down 1" and "drop down 2". DD1 controls DD2.

DD1 had a list with 3 options--I set that input range manually.

I created 3 ranges on Sheet2.
A_Range
B_Range
C_Range

Then I assigned this macro to DD1:

Option Explicit
Sub DD1Change()
Dim DD1 As DropDown
Dim DD2 As DropDown
Dim myRng As Range

Set DD1 = ActiveSheet.DropDowns("Drop down 1")
Set DD2 = ActiveSheet.DropDowns("Drop down 2")

With DD1
Set myRng = Nothing
Select Case .ListIndex
Case Is = 1: Set myRng = Worksheets("sheet2").Range("A_Range")
Case Is = 2: Set myRng = Worksheets("sheet2").Range("b_Range")
Case Is = 3: Set myRng = Worksheets("sheet2").Range("c_Range")
End Select
End With

If myRng Is Nothing Then
MsgBox "Design error!!!!"
Else
DD2.ListFillRange = myRng.Address(external:=True)
DD2.ListIndex = 0
End If

End Sub

 

[Lynda]

Dave, Thank you, thank you, thank you, you are a legend.

Cheers