dependant Combo Boxes

[depcdivr]

I have several lists located in a spreadsheet. For instance


Prod 1 Option1 Option2 option3
a d g
b e h
c f i

Prod 2 Option1 Option2 Option3 Option 4
1 4 7 9
2 5 8 11
3 6 9
4 10

In case the test does not translate very well, Option1 is located in ColumnB, Option 2 in ColC etc.

The starting Row for each product is different and each option may have a different number of selections in it.

I want to use a combobox to select a product and then show a combobox for each option for that product.


Given the example above if Prod1 is selected from the combobox then three comboboxes will appear populated as follows

Combobox1 = (a, b, c)
Combobox2 = (d, e, f)
Combobox3= (g, h, i)

I can do this by using the following code

If Me.Product.Value = "Prod1" Then
Set ws = Worksheets("RGA")
Option1.Visible = True
Option2.Visible = True
Option3.Visible = True
Else
Option4.Visible = False
Option5.Visible = False
Option6.Visible = False
End If
 
For Each cPart In ws.Range("b2:b19")
With Me.Combobox1
.AddItem cPart.Value
.List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
End With
Next cPart
For Each cPart In ws.Range("c2:C19")
With Me.Combobox2
.AddItem cPart.Value
.List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
End With
Next cPart
For Each cPart In ws.Range("d2:d19")
With Me.Combobox3
.AddItem cPart.Value
.List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
End With
Next cPart
 
[\code]
 
I was wondering if there were an easier way since I have a total of 50 products to do this for and would hate to have to cut/paste/modify this code that many times. Thanks for the help.