Home / Userform / Dependent Combo Boxes in a Userform

Dependent Combo Boxes in a Userform

 

In this post we will develop an excel workbook that contains a Userform with dependent combo boxes. The Userform will look like this:

 

 

 

 

 

 

The user selects Fiction from a drop-down list. As a result, the user can select Fiction category books from a second drop-down list. In another example the user selects Romance from the first drop-down list. As a result, the user can select a Romance category books from a second drop-down list.

 

 

 

 

 

 

Now to create this Userform, execute the following steps.

Step 1 – Open Editor and Create Userform

Open the Visual Basic Editor. If the Project Explorer is not visible, click View, Project Explorer.

Click Insert, Userform. If the Toolbox does not appear automatically, click View, Toolbox. Your screen should be set up as below.

Step 2 – Add the Combo Boxes

Add the combo boxes (first at the left, the second at the right) and command button. Once this has been completed, the result should be consistent with the picture of the Userform shown earlier. For example, create a combo box control by clicking on ComboBox from the Toolbox. Next, you can drag a combo box on the Userform.

You can change the names and the captions of the controls. It is good practice to change the names of the controls, but it is not necessary here because we only have a few controls in this example. To change the caption of the Userform and command button, click View, Properties Window and click on each control.

Step 3 – Show the Userform

To show the Userform, place a command button on your worksheet and add the following code line:

Private Sub CommandButton1_Click()
 
    UserForm1.Show
 
End Sub

We are now going to create the Sub UserForm_Initialize. When you use the Show method for the Userform, this sub will automatically be executed.

Step 4 – Setup Data for ComboBoxes

Open the Visual Basic Editor, in the Project Explorer, right click on UserForm1 and then click View Code. Choose Userform from the left drop-down list. Choose Initialize from the right drop-down list.  Add the following code lines:

Private Sub UserForm_Initialize()
 
    With ComboBox1
        .AddItem "Fiction"
        .AddItem "Romance"
        .AddItem "Science - Fiction"
    End With
 
End Sub

These lines of code fill the first combo box. We have now created the first part of the Userform. To test the Userform start it from the button on the worksheet. You will see the first list of options available. When you select one of those nothing will happen in the second list as we need to add the code that populates that Combobox based on the first selection

In the Project Explorer, double click on UserForm1, double click on the first combo box. Add the following code lines:

Private Sub ComboBox1_Change()
 
Dim index       As Integer
 
    index = ComboBox1.ListIndex
    ComboBox2.Clear
 
    Select Case index
        Case Is = 0
            With ComboBox2
                .AddItem "To Kill a Mockingbird"
                .AddItem "1984 by George Orwell"
                .AddItem "Pride and Prehudice"
                .AddItem "Harry Potter and the Sorcerer's Stone"
            End With
        Case Is = 1
            With ComboBox2
                .AddItem "Dark Lover"
                .AddItem "Twilight"
                .AddItem "Dead Until Dark"
                .AddItem "Darkfever"
            End With
        Case Is = 2
            With ComboBox2
                .AddItem "Ender's Game"
                .AddItem "Dune"
                .AddItem "Brave New World"
                .AddItem "Foundation"
            End With
    End Select
 
End Sub

Step 5 – Set the value on the Worksheet

Double click on the Import button. Add the following code line:

Private Sub CommandButton1_Click()
 
    Range("A1").Value = ComboBox2.Value
 
End Sub

To help you get started here is a link to a Sample Workbook that includes the Macro discussed above.

 

Download Sample Workbook Here

 

If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment.

About Excel Help Desk