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!
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,494
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?
 

Big Monkey

Active Member
Joined
Nov 5, 2005
Messages
255

ADVERTISEMENT

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.
 

Big Monkey

Active Member
Joined
Nov 5, 2005
Messages
255
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...
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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
 

Big Monkey

Active Member
Joined
Nov 5, 2005
Messages
255
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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
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.
 

Big Monkey

Active Member
Joined
Nov 5, 2005
Messages
255
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. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,732
Messages
5,573,903
Members
412,555
Latest member
mark84
Top