Combo box to combo box with named ranges

[Simmerin]

Hi all,

I'm trying to setup a form where you select from one combo box a named range, then the second combo box fills with the values of the named range

i.e. you have a customer drop down with macrohard, orange, and lionux
and you select macrohard

then the contact list combo box is the contents of the named range macrohard and you can select people that work there i.e. William Fences but not people that work at the other companies i.e. Stephen Occupations

 

[Simmerin]

also, i dont want to do this with data validation, im trying to get this all on a pop up on startup form, i have the rest of the form working including the combobox where you select the named range. the last piece to this puzzle is the contact combo box

 

[Norie]

If the values in the first listbox are the named ranges just use the selected value to populate the 2nd listbox.

Combobox2.List= Range(Combobox1.Value).Value

 

[Simmerin]

Awesome! thanks for the reply, does that just go in the change area?

 

[Simmerin]

I've tried this code with the necessary name changes, in both the change and initialize locations and i get nuffin......

 

[Norie]

Where exactly did you put the code?

It should go in the change event of the first combobox.

If the names when wrong you would have got an error.

 

[Simmerin]

oh i placed it in the change of the second, i shall fix and see

Edit: ok so now it works perfectly!.....if there is only one cell in the named range, i.e. it works with the customers that only have one contact. but the multiple contacts bring back a runtime error

 

[Simmerin]

run-time error '-2147352571

could not set the Value property. Type mismatch

 

[Norie]

What exact code did you try?

Also what ranges do they named ranges cover?

 

[Simmerin]

Private Sub cmbCustomer_Change()
cmbContact.Value = Range(cmbCustomer.Value).Value
End Sub

The named ranges select the contact names only
so for example, range name crabshack selects joe crab, and snow crab

also, the first drop down is

Me.cmbCustomer.List = Range("rangenamenozero").Value