MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Cascading combo boxes

  Asked By: Lourdes    Date: Nov 28    Category: MS Office    Views: 1350

I have a situation where I want my users to select a type of product
in combo box 1 and for combo box 2 to then display the relevant
product list. I swear I found a page on this on the net last week but
I cannot find it again. Can anyone advise if there is a relatively
simple way to accomplish this please.



3 Answers Found

Answer #1    Answered By: Adelaide Fischer     Answered On: Nov 28

Your terminology is a bit confusing.
"Cascading" would imply that you want a separate form to
be displayed.
But your description seems to be as if you have two comboboxes on the
same form...

Are you using a ComboBox in a Form?
or are you referring to a drop-down list  on a spreadsheet?
Where is your source data for the combo-box?

As an example, I set up a sheet with the cells starting at A1 with:

Red Orange Yellow Green Blue Indigo Violet
Red-Red Orange-Red Yellow-Red Green-Red Blue-Red
Indigo-Red Violet-Red
Red-Orange Orange-Orange Yellow-Orange Green-Orange Blue-
Orange Indigo-Orange Violet-Orange
Red-Yellow Orange-Yellow Yellow-Yellow Green-Yellow Blue-
Yellow Indigo-Yellow Violet-Yellow
Red-Green Orange-Green Yellow-Green Green-Green Blue-
Green Indigo-Green Violet-Green
Red-Blue Orange-Blue Yellow-Blue Green-Blue Blue-
Blue Indigo-Blue Violet-Blue
Red-Indigo Orange-Indigo Yellow-Indigo Green-Indigo Blue-
Indigo Indigo-Indigo Violet-Indigo
Red-Violet Orange-Violet Yellow-Violet Green-Violet Blue-
Violet Indigo-Violet Violet-Violet

(sorry about the word-wrap)

thank I created a form with (2) comboboxes and a cancel/OK button.

The actions I used were:

Private Sub UserForm_Initialize()
For col = 1 To 7
ColorSel.ComboBox1.AddItem (Cells(1, col))
Next col
End Sub
Private Sub ComboBox1_Change()
For col = 1 To 7
If (ColorSel.ComboBox1.Value = Cells(1, col)) Then
Exit For
Next col
For rnum = 2 To 8
ColorSel.ComboBox2.AddItem (Cells(rnum, col))
Next rnum
End Sub

Private Sub Btn_Cancel_Click()
Unload ColorSel
End Sub

Private Sub Btn_OK_Click()
MsgBox "Selected:" & Chr(13) & ColorSel.ComboBox2.Value
End Sub

this loads the first combobox with the headings.
the change event then searches for a matching header and
loads combobox2 with the values in that column.

look like what you're after?

Answer #2    Answered By: Blake Smith     Answered On: Nov 28

Here's one approach:
1) Define a range containing your types of products (say "Type")
2) For each "Type" define a range with the list  of products
3) In any cell (say A1 for your box  1)use Data Validation to create
a drop down of the types by selecting list and putting "=Type"
in the source box.
4) In another cell (Say A2 for your box 2) use Data Validation,
select list, and put "=INDIRECT(A1)" in the source box.
When the user selects a product type  in A1, the drop down in A2 will
contain the list of products for that type.

Answer #3    Answered By: Ryder Anderson     Answered On: Nov 28

I managed to figure out a solution myself over the weekend and did it
different to the suggestions. So for anyone else having the same
problem, here's how I solved it:

NB I have only been learning VBA for a week so this may not be the
best way to do it!!

Private Sub Type_Change()
If Type.Value = "Option1" Then Product.RowSource = "Option1List"
If Type.Value = "Option2" Then Product.RowSource = "Option2List"
If Type.Value = "Option3" Then Product.RowSource = "Option3List"
End Sub

Where the Option?List is a named range in the spreadsheet.

Please if anyone has an opinion on this method then let me know cos I
am just learning!.

Didn't find what you were looking for? Find more on Cascading combo boxes Or get search suggestion and latest updates.