UserForm

[grahammal]

I have a dropdown menu in cell A21 called 'equip'.
Cell B21 has dropdown menu's dependant on A21
=INDIRECT(SUBSTITUTE(A21," ","")).
This works fine.

I have generated a UserForm with two ComboBoxes on it, 1 & 2.
ComboBox1 has the same dropdown menu that cell A21 has, and works
fine.
I want ComboBox2 to have dropdown menus like cell B21 but to be
dependant on ComboBox1.
How do I program ComboBox2 to do this.
I assume it's something to do with RowSource but dont know how to do
it.

 

[Bob Phillips]

You could use this

Private Sub ComboBox2_DropButtonClick()
Me.ComboBox2.RowSource = Range(Replace(ComboBox1.Value, " ",
"")).Address
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[L Scholes]

If I'm going to use this code for my list, where do I enter the raw
data that should be available in ComboBox2 in relation to the data
available to ComboBox1? (I hope I said that right.)

 

[grahammal]

Have tried your suggestion but the syntax stays highlighted in red and
dos'nt seem to do anything to ComboBox2.

 

[Bob Phillips]

NG wrap-around. Try

Private Sub ComboBox2_DropButtonClick()
Me.ComboBox2.RowSource = Range(Replace( _
ComboBox1.Value, " ", "")).Address
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bob Phillips]

This is a set of named ranges.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[grahammal]

Have cured the wrap around problem OK but still don't get ComboBox 2 to
achieve the desired effect.
Do I need to anything about the Address bit at the end.

 

[Bob Phillips]

Should be okay, but maybe try external referencing

Private Sub ComboBox2_DropButtonClick()
Me.ComboBox2.RowSource = Range(Replace( _
ComboBox1.Value, " ", "")).Address(, , , True)
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[grahammal]

Have tried your suggestion but still just have an empty dropdown box for
ComboBox2.
Sorry to be a pain.

 

[Bob Phillips]

Can you post your workbook somewhere on the web? www.cjoint.com has a free
hosting service.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[grahammal]

Workbook is over 20Mb in size.
Wish CJoint was in English.

I'm sure someone out there can solve my problem, have lots of faith

 

[Bob Phillips]

What I gave you works, I tried it several times to make sure. Can you not
pare the workbook down to demonstrate the problem.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[grahammal]

I have written a small Workbook that demonstrates my problem. How can I
get it to you??

 

[Bob Phillips]

Post it at cJoint. It is in French, but the first box has a button called
Browse beside it. Click that and browse to your file, then click the Créer
le lien Cjoint button.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[grahammal]

Have resolved most of the problem now with your help, the only bit it
dos'nt like now is the word 'Replace'.
If I use
Me.ComboBox2.RowSource = Range(ComboBox1.Value).Address(, , , True)
It works fine but of cause I do need the 'Replace' option or an
equivelant to
take care of the range names with spaces in them.

 

[Bob Phillips]

Long-shot perhaps. In the VBIDE, goto Tools>References, and see if any items
are flagged as MISSING. If so, uncheck it.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)