Dave Peterson...HELP!!!!

[Lynda]

Hi Dave,
Recently you helped me with dependant drop down lists. You wrote code for me
which I modified to suit my circumstances. I now have another sheet which has
3 dropdown lists. 3 being dependent on 2 which in turn is dependent on 1.
Below is my modified code but now I need to know how to create the scenario I
have stated above. For example in Case 2 the range is ("P26") where in the
third list P2 has the range (Q4:Q15) and P3 has a range (Q16:Q19) and so on.
I having been trying to work it out but I can’t get it to work, can you help
me please.

Cheers
Lynda



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

Set DD1 = ActiveSheet.DropDowns("Drop down 10")
Set DD2 = ActiveSheet.DropDowns("Drop down 11")

With DD1
Set myRng = Nothing
Select Case .ListIndex
Case Is = 1: Set myRng = Worksheets("sheet2").Range("P11")
Case Is = 2: Set myRng = Worksheets("sheet2").Range("P26")
Case Is = 3: Set myRng = Worksheets("sheet2").Range("P77")
Case Is = 4: Set myRng = Worksheets("sheet2").Range("P811")
Case Is = 5: Set myRng = Worksheets("sheet2").Range("P1215")



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

 

[Dave Peterson]

So you need another macro for that second dropdown.

I fiddled with the names of the dropdowns and the addresses of the ranges, but
this worked ok for me:

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

'dd1 is the one you changed--no need to assign by name.
Set DD1 = ActiveSheet.DropDowns(Application.Caller)
Set DD2 = ActiveSheet.DropDowns("Drop down 2")
Set DD3 = ActiveSheet.DropDowns("Drop down 3")

With DD1
Set myRng2 = Nothing
Select Case .ListIndex
Case Is = 1: Set myRng2 = Worksheets("sheet2").Range("B1:B5")
Case Is = 2: Set myRng2 = Worksheets("sheet2").Range("B2:B6")
Case Is = 3: Set myRng2 = Worksheets("sheet2").Range("B3:B7")
Case Is = 4: Set myRng2 = Worksheets("sheet2").Range("B4:B8")
Case Is = 5: Set myRng2 = Worksheets("sheet2").Range("B5:B9")
End Select
End With

If myRng2 Is Nothing Then
MsgBox "Design error!!!!"
Else
DD2.ListFillRange = myRng2.Address(external:=True)
DD2.ListIndex = 0
'dd3 gets reset to nothing
DD3.ListFillRange = ""
End If

End Sub
Sub DD2Change()
Dim DD2 As DropDown
Dim DD3 As DropDown
Dim myRng3 As Range

'dd2 is the one you changed--no need to assign by name.
Set DD2 = ActiveSheet.DropDowns(Application.Caller)
Set DD3 = ActiveSheet.DropDowns("Drop down 3")

With DD2
Set myRng3 = Nothing
Select Case .ListIndex
Case Is = 1: Set myRng3 = Worksheets("sheet2").Range("C1:C5")
Case Is = 2: Set myRng3 = Worksheets("sheet2").Range("C2:C6")
Case Is = 3: Set myRng3 = Worksheets("sheet2").Range("C3:C7")
Case Is = 4: Set myRng3 = Worksheets("sheet2").Range("C4:C8")
Case Is = 5: Set myRng3 = Worksheets("sheet2").Range("C5:C9")
End Select
End With

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

End Sub

You'll assign the dd1change code to the first dropdown. And the DD2Change code
to the second dropdown. The third dropdown doesn't get a macro.

And you can rename those dropdowns if you want.

Just rightclick on it and type the new name in the NameBox--to the left of the
formula bar. Remember to hit enter when you're done typing the new name.

=====
In general, each new dropdown will get its own macro--except for the last.

And each macro has to clear out any dropdowns that are further down the "food
chain".

 

[Lynda]

Thank you Dave, it took me a while to get it to work but it is going great
now. Thanks heaps.

Cheers
Lynda