Dropdown Rowsource

[Aechelon]

I have a sheet with multiple columns and rows.

Column A is a category description
Column B is a subcategory description.

I'm setting up a userform with dropdown, or combo boxes. What i want
is on the change event of box 1, i want to set the rowsource property
of box 2 to return all subcategory values that have the same category
value. For example
A B
1 Over Me
2 Over You
3 Over Us
4 Under Them
5 Under Everyone

If I select "Over" In box 1, i need the range "B1:B3" for rowsource in
box 2. Help!

 

[Dave Peterson]

I think I'd take a slightly different approach. I'd put my categories in a
separate range (name it cate??).

And I'd put each subcategory in it's own range (column?) and name them nicely
(Over and under??).

Then I could just change the list of entries based on that selection and those
ranges:

Option Explicit
Private Sub ComboBox1_Change()

Me.ComboBox2.Clear

Select Case LCase(Me.ComboBox1.Value)
Case Is = "over"
Me.ComboBox2.List = Worksheets("sheet1").Range("Over").Value
Case Is = "under"
Me.ComboBox2.List = Worksheets("sheet1").Range("under").Value
End Select

End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Me.ComboBox1.List = Worksheets("sheet1").Range("cate").Value
End Sub

But you could do it by looping through the rows, too:

Option Explicit
Private Sub ComboBox1_Change()
Dim myCell As Range
Dim myRng As Range

With Worksheets("sheet1")
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

Me.ComboBox2.Clear

For Each myCell In myRng.Cells
If LCase(myCell.Value) = LCase(Me.ComboBox1.Value) Then
Me.ComboBox2.AddItem myCell.Offset(0, 1).Value
End If
Next myCell
End Sub