multiple combo boxes in a user form...

Big Monkey

Active Member
Joined
Nov 5, 2005
Messages
255
There are so many threads on user forms and combo boxes, I gave up after reading for over 1.5 hours and popped in a DVD.

I just started creating my first user form, and I have to say it is pretty neat stuff. So far I have only gotten as far as the layout, no code at all yet.

I have various labels, a few frames, radio option buttons, a couple of text boxes, and a couple of combo boxes.

It is looking real good to me, but now I have to make it actually do something with code. Of course I know almost nothing about VBA code, and this forum has certainly been a godsend for me.

OK, so here is my deal: I have two combo boxes at the moment. I have various defined name ranges which reference lists on a worksheet. Initially I was having trouble finding out how to input the list I needed in combo box 1. After some reading, I found I could simply input my defined name list in the RowSource property. Awesome! I was happy to progress.

However, now I want for the second combo box to populate other defined lists depending on the value of combo box 1. A couple of weeks ago, I learned that this could be done in a cell by using the INDIRECT function and referencing the validation list. I don't know if this is possible to do in code, but I sure haven't found how to go about it.

Any help guys?

P.S. Happy Thanksgiving!
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Hi big Monkey,

I think you're not getting any response because your queston is too vague.

Presumably on your useform you have 2 comboboxes and a command button. Can you post an example of what's in ComboBox1 and ComboBox2, and what you want to happen when you press the command button?
 
Upvote 0
al_b_cnu said:
Hi big Monkey,

I think you're not getting any response because your queston is too vague.

Presumably on your useform you have 2 comboboxes and a command button. Can you post an example of what's in ComboBox1 and ComboBox2, and what you want to happen when you press the command button?

Ahh, sorry. Ok then, let me try again.

I have a list defined that has states and that is what is available in combo box 1(that part I have been able to do). Let's say in combo box 1, a user chooses "Texas":

In combo box 2, I want the choices to be cities within Texas. Now I have lots of other lists which will be used to fill combo box 2. I need help with coding how to make cb2 put the matching lists when box 1 has a value in it.

Hope that clears it up a bit.
 
Upvote 0
Andrew Poulsom said:

Hey thanks Andrew. That actually works really well and does exactly what I want. The only thing is that the named ranges I use won't work with the code.

I can probably work around that if I just rename everything to use the "List", "List1", "List2" format you have going.

Any clue on why my named ranges won't function properly? My primary list is named "States". My secondary named ranges are "TexasCities", "CaliforniaCities" etc...
 
Upvote 0
Assumtions:

1. You have a range named States containing Texas and California, being the RowSource for ComboBox1.
2. You also have ranges named TexasCities and CaliforniaCities containing the relevant cities.

Try this code:

Code:
Private Sub ComboBox1_Change() 
    ComboBox2.RowSource = ComboBox1.Value & "Cities"
    ComboBox2.ListIndex = -1 
End Sub
 
Upvote 0
Sweet!

OK I'm almost there Andrew. I set up my code to mimic yours, and set new ranges with those names. (Sorry if I didn't mention I was just making up examples, I won't be using states or cities...but I don't think that really matters).

Anywho, 2 road bumps here. 1.) The code isn't working for me. I think it is because I don't have a named range that is "Cities". I tried to define a new range with that name, only I couldn't figure out how to make it refer to the other two already defined ranges. I could only make it apply to one by entering "=TexasCities" for example.

I tried adding a comma and a semicolon, but neither worked. Even then, with "Cities" referencing "=TexasCities"...once I tried to select the other when testing the user form, it gives me an error.

Could you do me a favor and let me know how you have your ranges defined in your working code so I know what I'm missing here?

Thanks for hanging in there man, I really don't mean to be a pest. I'm learning a lot though and I am trying to help out where I can in other threads if I know an answer.
 
Upvote 0
Big Monkey

How is it not working? Where have you put the code?

As far as I can see you don't need a named range Cities.

Cities is being used in the code as a string to build the named range using the value of the first combobox.

For example if you selected California from the 1st combobox the second combobox's rowsource should be set to CaliforniaCities.
 
Upvote 0
Oops. OK well it looks like I had input the code wrong and that is what was causing my errors. My combo boxes are named a bit differently, and I accidently named combobox2 in the 2nd half of the 2nd line of code instead of combobox1.

OK cool, so now it is working fine. Thanks a bunch guys! I'm gonna work on it some more to see if I can get them all to work like I want. :)
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top