cascading combo boxes

[dkinnz]

I'm not sure if "cascading combo boxes" is the correct term, but this is what I'm trying to do...

I want to have two combo-boxes, cboProduct and cboPart, such that based on what you select from cboProduct will limit the choices in cboPart.

cboProduct will contain 2 choices (A and B), and cboPart contains 4 total choices (W, X, Y and Z), that would be shortened to 2 choices based on each selection from cboProduct.
So, if you select A in cboProduct, your only options in cboPart would be W and X. Likewise, if you select B in cboProduct, your choices would be Y and Z in cboPart.

This is an extremely simplified case from what I need, but if I can get this figured out I'll be fine.

So I guess the question is: How do you link two combo boxes in vba?

If this question has already been answered and I've missed it in my search, plz post the link!

Cheers
-dkinnz

[MSP77079]

One way to do this would be to fill ComboBox2 when the user makes a selection from ComboBox1.

Private Sub ComboBox1_Change()
Dim rng As Range
    Set rng = Range(Me.ComboBox1.ListFillRange)
    Me.ComboBox2.Clear
    strValue = rng.Cells(Me.ComboBox1.ListIndex)
    
     set rng = Range(that contains the bigger list)
     For Each c In rng.Cells
        If c.Value = strValue Then
            Me.ComboBox2.AddItem c.Offset(0, 1)
        End If
    Next c
    
End Sub

[dkinnz]

thanks for the response, but I'm not sure if I follow the code...

On a larger scale (as in my application), I'm going to have 10 different items in ComboBox1, and based on each selected item, ComboBox2 will have about 15 choices.
Would your code still work in this case?

Also, please explian "Range that contains the bigger list".

Thank you,
dkinnz

[MSP77079]

Maybe I did not understand the original question ?

First I was assuming that there is a list used to fill Combo1, and that it would look something like this:

List that fills Combo1
A
B

Then, I was assuming there is a "bigger list", from which you only choose the items for Combo2 where the first column agrees with the selection in Combo1, something like this:

List Used to decide what goes in Combo2
Product Part
A W
B X
A Y
B Z

If my assumptions are incorrect, the suggested code would need to be revised. But, this is how it works ...

First, it grabs the range that was used to fill Combo1

Set rng = Range(Me.ComboBox1.ListFillRange)

Then, reads which item was selected

strValue = rng.Cells(Me.ComboBox1.ListIndex)

Next, you need to tell it where to find the "bigger list", so it can decide what needs to populate Combo2:

set rng = Range(that contains the bigger list)

After that, it reads down the list and if it finds an entry that is what what chosen in Combo1, it takes the value in the adjacent column and adds it to Combo2's list.

If you have more than 2, the code will need to be more sophisticated, I'm afraid. Any way you can post a skeleton of your workbook?