Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 115
Default Dave Peterson...HELP!!!!

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 ("P2:P6") 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 cant 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("P1:P1")
Case Is = 2: Set myRng = Worksheets("sheet2").Range("P2:P6")
Case Is = 3: Set myRng = Worksheets("sheet2").Range("P7:P7")
Case Is = 4: Set myRng = Worksheets("sheet2").Range("P8:P11")
Case Is = 5: Set myRng = Worksheets("sheet2").Range("P12:P15")



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

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Dave Peterson...HELP!!!!

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 wrote:

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 ("P2:P6") 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 cant 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("P1:P1")
Case Is = 2: Set myRng = Worksheets("sheet2").Range("P2:P6")
Case Is = 3: Set myRng = Worksheets("sheet2").Range("P7:P7")
Case Is = 4: Set myRng = Worksheets("sheet2").Range("P8:P11")
Case Is = 5: Set myRng = Worksheets("sheet2").Range("P12:P15")

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
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 115
Default Dave Peterson...HELP!!!!

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

Cheers
Lynda

"Dave Peterson" wrote:

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 wrote:

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 ("P2:P6") 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("P1:P1")
Case Is = 2: Set myRng = Worksheets("sheet2").Range("P2:P6")
Case Is = 3: Set myRng = Worksheets("sheet2").Range("P7:P7")
Case Is = 4: Set myRng = Worksheets("sheet2").Range("P8:P11")
Case Is = 5: Set myRng = Worksheets("sheet2").Range("P12:P15")

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Dave Peterson Rich_Patterson Excel Discussion (Misc queries) 2 January 26th 07 08:56 PM
Dave Peterson joelbeveridge Excel Discussion (Misc queries) 1 August 4th 06 02:55 AM
Mr Dave peterson, Please help TUNGANA KURMA RAJU Excel Discussion (Misc queries) 5 December 7th 05 05:11 AM
Dave Peterson atxcomputers Excel Discussion (Misc queries) 12 September 28th 05 06:17 PM
to Dave Peterson Ross Excel Discussion (Misc queries) 2 September 18th 05 04:45 PM


All times are GMT +1. The time now is 08:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"