Combobox & named ranges

[Bernieg]

Hi
I have 2 comboboxs in a userform
box2 = Part no. & box1= Op No.

op no is based on part no., the code below does work....however
if the form is opened on a page that is not the source of the named ranges the rowsource only reflects the actual range of the named range
i.e A6:A36 it does not know what page the info is on.

if i manualy type in = NQF005025 in the rowsource it workes

Private Sub ComboBox2_Change()
ComboBox1.Value = "" ' value empty by default
'changes the rowsource data(named range)of combobox1 based on the text selected in combobox2
ComboBox1.RowSource = ActiveWorkbook.Names(ComboBox2.Value).RefersToRange.Address
End Sub

Bernie

 

[Norie]

Bernie

If you can manually type the name for the RowSource you should be able to do it in code.

ComboBox1.RowSource = ComboBox2.Value

Have you tried that?

 

[Bernieg]

Thanks Norie.. so easy

 

[Norie]

Cheers, I thought you were going to come back and say it didn't work because it did seem kind of easy.

Thought I must have missed something somewhere.

PS Knowing my luck you'll post back in 5 minutes saying it didn't work.

 

[Bernieg]

Hi Norie
I also pre load the part no. combos wih named ranges

ComboBox2.RowSource = "parts"
ComboBox3.RowSource = "parts"

Do you know how to change the combobox forecolor in code

Bernie

 

[Norie]

Bernie

I though you only had 2 comboboxes?

Anyway, what is it you want to do?

Change the font (fore) colour based on what's in the combobox, what's selected in it...

 

[Bernieg]

I have some checkboxs with some code that checks at least 1 is checked
If not it turn the forecolor red with message

Bernie

 

[Norie]

Bernie

I'm sorry, I must be missing something here.

What exactly do you have? Comboboxes and checkboxes?