Dependent Combo Boxes
Below we will look at a program in Excel VBA which creates a Userform that contains dependent combo boxes. The Userform we are going to create looks as follows:
The user selects Animals from a drop-down list. As a result, the user can select an animal from a second drop-down list.
The user selects Sports from a drop-down list. As a result, the user can select a sport from a second drop-down list.
To create this Userform, execute the following steps.
1. Open the Visual Basic Editor. If the Project Explorer is not visible, click View, Project Explorer.
2. Click Insert, Userform. If the Toolbox does not appear automatically, click View, Toolbox. Your screen should be set up as below.
3. 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.
4. You can change the names and the captions of the controls. Names are used in the Excel VBA code. Captions are those that appear on your screen. 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.
5. 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.
6. Open the Visual Basic Editor.
7. In the Project Explorer, right click on UserForm1 and then click View Code.
8. Choose Userform from the left drop-down list. Choose Initialize from the right drop-down list.
9. Add the following code lines:
Private Sub UserForm_Initialize()
With ComboBox1
.AddItem “Animals”
.AddItem “Sports”
.AddItem “Food”
End With
End Sub
Explanation: These code lines fill the first combo box.
We have now created the first part of the Userform. Although it looks neat already, nothing will happen yet when we select an item from the first combo box.
10. In the Project Explorer, double click on UserForm1.
11. Double click on the first combo box.
12. 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 “Dog”
.AddItem “Cat”
.AddItem “Horse”
End With
Case Is = 1
With ComboBox2
.AddItem “Tennis”
.AddItem “Swimming”
.AddItem “Basketball”
End With
Case Is = 2
With ComboBox2
.AddItem “Pancakes”
.AddItem “Pizza”
.AddItem “Chinese”
End With
End Select
End Sub
Explanation: Excel VBA uses the value of the variable index to test each subsequent Case statement to see with which items the second combo box should be filled. Go through our Select Case program to learn more about the Select Case structure.
13. Double click on the Import button.
14. Add the following code line:
Private Sub CommandButton1_Click()
Range(“A1”).Value = ComboBox2.Value
End Sub
Result:
Next Chapter: Range
