L
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
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