Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Dave Peterson...Oh no, not her again...
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...Oh no, not her again...
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 its 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 35,218
Default Dave Peterson...Oh no, not her again...
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 wrote:

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 its 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 115
Default Dave Peterson...Oh no, not her again...
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" wrote:

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

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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 35,218
Default Dave Peterson...Oh no, not her again...
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.



Lynda wrote:

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

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

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


--

Dave Peterson
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

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...HELP!!!! Lynda Excel Discussion (Misc queries) 2 October 1st 08 01:47 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 09:34 AM.

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"

 

Contact Us ExcelBanter forum home Privacy Statement