tale of 2 combo boxes... I need help

[Ganther]

I have a userform that I have 2 combo boxs on. I have populated combobox1 using:

Private Sub UserForm_Initialize()
combobox1.RowSource = "maintenence"

Maintenence is my main named range consisting of about 10 items listed. now depending on what is selelcted in combobox1 I need combobox2 to populate with the corresponding range king of like a submenu.

Example

If service is selected in combobox1, I need combobox2 to be populate with my range named service.

If fluids is selected in combobox1, I then need combobox2 populated with my list of fluids.

And of course if I change my mind and select something different I need box2 to populate accordingly.

I am thinking the code has to go under the combobox1 change() but I am not sure what I need to put to make this happen..

Any help pushing me in the right direction would be great

 

[Norie]

If the names of the items in the first combobox match the named ranges this should work.

Private Sub ComboBox1_Change()
       ' check something has been entered in the listbox
       If ComboBox1.ListIndex <> -1 Then
               ComboBox2.List = Range(ComboBox1.Value).Value
       End If
End Sub

 

[Ganther]

I will give that a try and thanks for the quick reply

 

[Ganther]

Ok I tried what you suggested, it did not work it errored on me. So I tried the below and it worked.


If combobox1.ListIndex <> -1 Then
combobox2.RowSource = (combobox1.Value)

But I have 2 things in combobox1 that do not have a named range assosiated with them and if one of those 2 are selected it errors out as well. I could make a blank named range for those 2 items.

 

[Norie]

How did the code I posted error?

It worked for me when I tested it.

As for the 2 items not associated with ranges, what should the second combobox be populated with when they're selected?

I don't think it would be a good idea to create dummy named ranges to deal with them.

You could just check if what's selected is one of them and take appropriate action.