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.