Dave Peterson...Oh no, not her again...

[Lynda]

Dave, I don't know if I am allowed to be doing this, calling you up like
this, or if i should be just posting in general maybe you can let me know if
it is okay. Anyway I am sorry to keep annoying you it’s just that you wrote
the code for my previous two questions so this one would be familiar to you.
The problem I am having now is with the triple dropdowns you helped me with.
I wish I could show you my document as it is really hard for me to explain
what my problem is but here goes.
In my first dropdown I have four items.

A1 will return the range B1:B5 in dropdown 2. In dropdown 3, B1 will return
C1:C2, B2 will return C3:C14, and so on down to B33.
A2 will return the range B6 as requested but will not give me C34, it goes
back to C1:C2
All ranges connected to A1 work fine, but when I move to A2, A3 and A4
things start to go wrong, they will return what I ask for in dropdown 2 but
they keep returning to the ranges connect to A1 in dropdown 3. I have tried
making changes but just end up in a bigger mess than I started with. Sorry
for being so dumb but can you please help me again.........

Below is the code you wrote for me with my changes.
Option Explicit
Sub DD1Change()
Dim DD1 As DropDown
Dim DD2 As DropDown
Dim DD3 As DropDown
Dim myRng2 As Range

Set DD1 = ActiveSheet.DropDowns(Application.Caller)
Set DD2 = ActiveSheet.DropDowns("Drop down 4")
Set DD3 = ActiveSheet.DropDowns("Drop down 8")

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("B6:B6")
Case Is = 3: Set myRng2 = Worksheets("sheet2").Range("B7:B10")
Case Is = 4: Set myRng2 = Worksheets("sheet2").Range("B11:B12")
Case Is = 5: Set myRng2 = Worksheets("sheet2").Range("B13:B13")
End Select
End With

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

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

Set DD2 = ActiveSheet.DropDowns(Application.Caller)
Set DD3 = ActiveSheet.DropDowns("Drop down 8")

With DD2
Set myRng3 = Nothing
Select Case .ListIndex
Case Is = 1: Set myRng3 = Worksheets("sheet2").Range("C1:C2")
Case Is = 2: Set myRng3 = Worksheets("sheet2").Range("C3:C14")
Case Is = 3: Set myRng3 = Worksheets("sheet2").Range("C15:C18")
Case Is = 4: Set myRng3 = Worksheets("sheet2").Range("C19:C24")
Case Is = 5: Set myRng3 = Worksheets("sheet2").Range("C25:C33")
Case Is = 6: Set myRng3 = Worksheets("sheet2").Range("C34:C34")
Case Is = 7: Set myRng3 = Worksheets("sheet2").Range("C35:C35")
Case Is = 8: Set myRng3 = Worksheets("sheet2").Range("C36:C40")
Case Is = 9: Set myRng3 = Worksheets("sheet2").Range("C41:C45")
Case Is = 10: Set myRng3 = Worksheets("sheet2").Range("C46:C50")
Case Is = 11: Set myRng3 = Worksheets("sheet2").Range("C51:C51")
Case Is = 12: Set myRng3 = Worksheets("sheet2").Range("C52:C55")
Case Is = 13: Set myRng3 = Worksheets("sheet2").Range("C56:C66")
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



Thank you so much
Cheers
Lynda

 

[Dave Peterson]

By directing your question to me, you may find that other people will ignore
your post. I don't think I'd do it that way.

But I couldn't duplicate your situation.

Are you sure that you've named the dropdowns correctly--there are no dropdowns
with the same name, right????

When I built my test workbook, I put:
=cell("address",a1)
in Sheet2!a1
and dragged down and to the right so that I could see the addresses of the
ranges assigned.

And I only used 3 dropdowns--each with their own name.

 

[Lynda]

Thank you Dave, the only reason i posted directly to you was because you had
written the original code for me otherwise i wouldnt have posted directly to
you but i will do as you suggest next time.

I didn't change the names of the dropdowns but i checked the names of the
dropdowns in the name box and named them in the code accordingly.

dropdown 1 is called 'drop down 1', 2 is 'drop down 4' and 3 is 'drop down 8'.

When i select A3 in dd1 and B7 in dd2(4) it should return C34 in dd3(8) but
it doesn't it returns C1:C2 which is what i have set up for A1, B1.

 

[Dave Peterson]

If B7 is the top option in DD2, then its listindex is 1 and that means that the
code will use:

Case Is = 1: Set myRng3 = Worksheets("sheet2").Range("C1:C2")

Remember that the listindices are in order 1 to whatever.