Cascading ComboBoxes

[aaronlive]

I apologize if this is too elementary for some on the forum. I'm just trying to create a user form with two cascading comboBoxes (i.e. the list in box2 is dependent on the selection in box1). I've looked all over and have tried various code suggested on different forums, but I just can't get it to work. I think there are two parts to the question. First, what is the appropriate code? Second, where do I put the code (i.e. is it just a sub in the sheet or is it part of the combobox or somewhere else)?

Thanks in advance for any help.

 

[id107]

something like

Private Sub ComboBox1_Change()
 
    If ComboBox1.Value = "" Then
        ComboBox2.Clear
    ElseIf ComboBox1.Value = "option1 on combobox1" Then
        ComboBox2.Clear
        ComboBox2.SetFocus
        With ComboBox2
                .AddItem "first item"
                .AddItem "second item"
        End With
    ElseIf ComboBox1.Value = "option2 on combobox1" Then
        ComboBox2.Clear
        ComboBox2.SetFocus
        With ComboBox2
                .AddItem "third item"
                .AddItem "fourth item"
        End With
    End If
 
End Sub

You'll need to edit the items, but that's how I would go about it.

Edit: The code will need to go into the code on the Form itself. You can get to this by clicking the "View Code" icon in the VBA Project window or pressing F7 from the Form in the editor.

 

[aaronlive]

Thanks for the reply. Do you have a suggestion with using a Range as the source for box2? I've tried things like:

ComboBox2.RowSource = Range("A1:A5").Address

or

ComboBox2.RowSource = Range(Cells(Row_1, Col_2), Cells(Row_5, Col_2)).Address
Both of which didn't work.

 

[aaronlive]

Nevermind the last comment..... Figured it out....

Thank you id107!!

 

[aaronlive]

I wonder if you can help me with a related question. I'm trying to load the comboboxes with data from a different worksheet. However, no matter what code I try it only loads from the active sheet. Here's an example of one of the subs that I tried. Let me know if you have any ideas how to fix it. Appreciate the help!

Private Sub ComboBox1_Change()

If ComboBox1.Value = "" Then
ComboBox2.Clear
ElseIf ComboBox1.Value = "Income" Then

ComboBox2.RowSource = Worksheets("Sheet3").Range("F1", "F5").Address

ElseIf ComboBox1.Value = "Costs" Then
ComboBox2.RowSource = Worksheets("Sheet3").Range("G1", "G5").Address
End If

End Sub