Cascading comboboxes (nested dropdowns) in a userform

[innov]

Howdy folks.

I have exhausted all my efforts on this one. First off, I know there are posts for "cascading comboboxes", but they are not this situation.

I currently have a combobox that employs the .dropdown method upon clicking on the combobox (no big deal).

Upon selection from the dropdown list, I want to dropdown another list based on the selection from the 1st dropdown list. I cannot get a second list to dropdown once the 1st one has been displayed.

Any help is greatly appreciated.

 

[SkipVought]

Hi,

"I cannot get a second list to dropdown once the 1st one has been displayed."

What have you tried that has not worked. Please include any code as well.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[HughLerwill]

Have played with this many times in VB6 and have found it can quickly become a rats nest of update events if all the combos are displayed at the same time e.g. you select an item in Combo1 and it fires of an event to update Combo2 which fires off an event to re-update Combo1.
Consider placing the Combos on separate tabs on a Tab Control and only update each based on the selected item from the other(s) when its Tab is selected.

A quick and dirty to Drop a Combo;
ComboBox1.SetFocus
SendKeys "{F4}

 

[innov]

Skip and Hugh,

Thanks for the replies.
Skip: That's exactly my problem. Once a dropdown is displayed, I cannot display another without some user intervention. I have tried setting the 1st combobox to visible = false, setting focus on the 2nd combobox, you name it.

Attached is a simple set of code I'm using to test the code. Use "tektips" for both the username and password.

Hugh, your solution does not work once the 1st combobox dropdown is displayed. Additionally, I'd like to avoid function keys as I am constanting using/ demo-ing the software in a Webex type session and some function keys are hijacked.

I'm going to also try using a single combobox and just changing the list and see if that works.

My goal is to have it function similar to cascading pop-up menus in a web page.

THANKS FOR ANY ADDITIONAL FEEDBACK!

Ted

 

[innov]

I see how my link came out due to spaces. Here it is:

http://innovient.com/Downloads/cascading

combo test 2.xls

 

[SkipVought]

"Once a dropdown is displayed, I cannot display another without some user intervention."

Sorry to burst your bubble, but that's exactly what has to happen when you paint a combobox: the user must make a selection. THEN, based on the user selection, you paint another combobox, filling the list with the data resulting from the user selection.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[innov]

Hey Skip.

Nothing burst here. I DO want the user to make a selection from the 1st dropdown, and based on the selection, display a second dropdown. As you can see from my sample workbook, I'm using the _Change event on the combobox to trigger the 2nd dropdown, but can't get it to display.

BTW - I hadn't been in Tek-Tips for a good while and I was thrilled to see that you were still graciously offering your assistance / sharing your expertise. THANKS!!

 

[fumei]

I'm using the _Change event on the combobox to trigger the 2nd dropdown, but can't get it to display."

You can use the _Change event of one combo to, say, Clear and rebuild the items of another combo, but does not the _Change event have to finish first before focus can shift?

Gerry

 

[SkipVought]

Why are you not using ComboBox objects?

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[innov]

Gerry - I believe that is the issue. The Change event must finish before focus can shift. I tried firing off an asynch task with ONTIME (in a half-second or so), but that did not work either.

Skip - I'm not sure what you mean. Can you be more specific of offer an example?

 

[PHV]

Have you tried the AfterUpdate event instead of the Change ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fumei]

That may work, although I am still a bit unclear as to the exact conditions going on.

Gerry

 

[SkipVought]

The only control objects you have on your userform are a lable and a button.

You do not have ANY combobox control on your form. You have neatly contrived a lstone and lsttwo "dropdown" to be created on the fly, but not a ComboBox Control that is in your Toolbox (4th one from upper-left to right)

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

using your "dropdown" method, I added another lable named lblTwo and assigned the visible property as FALSE.

When the selection is made in lstOne, lblTwo is made visible. The lblTwo_Click event assigns the properties to lstTwo.

WORKS!

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[innov]

Skip,

I guess you saw that I do indeed have 2 comboboxes but they are hidden.

Just for context, I have a floating (non modal) dashboard for navigation, etc. and
upon clicking on a label, dyamically display JUST the dropdown (again to look similar to a pop-up / dropdown menu in a webpage.

I tried what you described and can indeed get the labelTwo to appear. Now I need to invoke the click event to do the dropdown without user intervention.
Does you code do this? If so, can you share?

THANKS!

 

[innov]

PH: I did try the AfterUpdate and it did not work either.

Skip: Does "WORKS!" mean that you got the second combobox to dropdown without clicking on the second label? That's my object.

Thanks.

 

[innov]

Here's another approach that might work, but I have not been able to get it going either: using a SINGLE combobox and modifying the list.

Upon selecting from the dropdown, the _change event fires. In the change event, I modify the list and redo the .dropdown to no avail.

If anyone can help me get the dropdown to display a second time with the new values, that would be great too.

Sample code:

(I call the combobox "lstOne")

The list is first filled with an array (this works fine so code is abbreviated):

With Me.lstOne
.List = Array("A","B","C")
.Dropdown
End With

Then in the combobox's change event:

Private Sub lstOne_Change()
With Me.lstOne
.List = Array("1","2","3")
.Dropdown
End With
End Sub