Combo Box w/Dependent Options

[Guest]

Hi

How can I setup combo boxes in a form so that when the user selects an option from the first box, the second one gets populated with a certain set of options? I tried to do this in Access, but Access seems more complicated than necessary, so for now I will settle for doing it in Excel if possible (and if I can find a solution that performs quickly). What I started to set up in Access was a table where every possible option in the second list (in one column) has a "category" (in a second column) that matches one of the options in another table. The first combo box displays these "category" options. I tried to do a SQL statement that referenced the selected option in the first combo box but it didn't work. It was accepted syntactically but the field said #Name? and never changed when I picked from the first list / combo box.

Thanks

 

[William]

Hi Gregg

On my worksheet, cell E1 equals "yes" and E2 equals "no". The range E1:E2 I
have named "yesno".
I have a list of data in, say, range A1:A10 that I have named "this" and
another list of data in say, range C10:C30 that I have named "that".

I have a Userform with two combo boxes. ComboBox1 has its rowsource as
"yesno". When I enter or change a value in ComboBox1, I have the following
code behind the userform.


Private Sub ComboBox1_Change()
ComboBox2.RowSource = ""
ComboBox2.Value = ""
If ComboBox1 = "yes" Then
ComboBox2.RowSource = "this"
Else
ComboBox2.RowSource = "that"
End If
End Sub


--
XL2002
Regards

William

(e-mail address removed)

| Hi,
|
| How can I setup combo boxes in a form so that when the user selects an
option from the first box, the second one gets populated with a certain set
of options? I tried to do this in Access, but Access seems more complicated
than necessary, so for now I will settle for doing it in Excel if possible
(and if I can find a solution that performs quickly). What I started to set
up in Access was a table where every possible option in the second list (in
one column) has a "category" (in a second column) that matches one of the
options in another table. The first combo box displays these "category"
options. I tried to do a SQL statement that referenced the selected option
in the first combo box but it didn't work. It was accepted syntactically but
the field said #Name? and never changed when I picked from the first list /
combo box.
|
| Thanks!
|

 

[Guest]

I've tried to make your code work, William, but I need to start at the very beginning. When I use the Forms toolbar, there is no button on it that I can see that will actually *create a form.* Controls get added to the sheet, but they are hanging in thin air in front of the cells in Sheet1, not on an opaque form. VBA apparently doesn't see them, or doesn't see them as the type of combo box you wrote code for, since it causes an error. It gives "object doesn't support this property or method" in your line about the RowSource. ListFillRange doesn't work either, which is the property that got used when I recorded the process of setting the property manually. Nor can I see much difference between the Help descriptions for RowSource or ListFillRange.

I have looked all over the Help concerning Userform and Forms and Add, and I can't find a *simple*, start-from-the-beginning explanation or example of how to programmatically create a new userform and add controls to it. Nor can I figure out how to do it manually and record my actions. There are many hints about doing it in VBA, but I can't make any of them work. I won't waste time ranting about the quality of MS' Help file, but I could use more help from someone here. :-(

TIA,

Greg

 

[stevem]

Ok, William is referring to using a UserForm with 2 comboboxes in tha
userform..

To add a userform, open the VBE (Tools -> Macros - > Visual Basi
Editor)

Then go to (Insert -> Userform)

There is a little tutorial that can get you started at:
http://tinyurl.com/2ynr

 

[Guest]

Outstanding! That page will keep me busy for a while.

Thanks so much!

Gregg

 

[stevem]

Glad I could be of help.

That whole site will keep you busy for awhile, and in facts have forum
that link directly into this newsgroup (is how I post to the newsgrou
myself)

 

[William]

Thanks Steve.

--
XL2002
Regards

William

(e-mail address removed)

| Ok, William is referring to using a UserForm with 2 comboboxes in that
| userform..
|
| To add a userform, open the VBE (Tools -> Macros - > Visual Basic
| Editor)
|
| Then go to (Insert -> Userform)
|
| There is a little tutorial that can get you started at:
| http://tinyurl.com/2ynrc
|
|
| ---
| Message posted
|