Souptik Roy
In this article, I will show you the procedures of cascading combo boxes in Excel VBA userform. While working in Excel, you may need to make some interactive forms. One of them is cascading combo boxes. In this article, I will show you the steps of the procedures to create a cascading combo box in the Excel VBA user form. This is a short and easy method. Follow the procedures carefully. I have added the necessary images for your convenience. I hope, this will help you to enhance your skill.
Download Practice Workbook
Please download the workbook to practice yourself.
Step-by-Step Procedures to Cascade Combo Boxes in Excel VBA Userform
In this article, I will consider a dataset given below. The dataset contains two columns, B & C, called Cricket Players and Football Players. The dataset is ranging from B4 to C10 cells. With this dataset, I will show you the procedures of cascading combo boxes with an Excel VBA user form. Follow the steps carefully.
Step 1: Inserting a UserForm
In this portion of this article, I will show you the procedures of inserting a UserForm with a ComboBox and CommandButton. Follow the steps given below.
- First, select the Insert menu item.
- Then select UserForm from the menu.
- Consequently, the Userform that we will construct appears as seen in the image below.
- Additionally, we will include command buttons and combo boxes.
- To add a combo box to the left and a second to the right, select position 3 in this case.
- The command button is then added by selecting position 4 in the image below.
Read More: How to Clear Items from VBA ComboBox in Excel
Step 2: Applying VBA Code
Here, I will implement a VBA code to run and create a cascading combo box. The procedure is easy. Just follow the steps carefully.
- Meanwhile, please right-click on the UserForm.
- Then, insert the following VBA code here, hit F5, and the code will run.
Private Sub UserForm_Initialize()
With ComboBox1
.AddItem "Cricket_Players"
.AddItem "Football_Players"
End With
End Sub
Private Sub ComboBox1_Change()
Dim Xind As Integer
Xind = ComboBox1.ListIndex
ComboBox2.Clear
Select Case Index
Case Is = 0
With ComboBox2
.AddItem "Sourav Ganguly"
.AddItem "Sakib Al Hasan"
.AddItem "Litton Das"
.AddItem "Adam Gilchrist"
.AddItem "Stuart Broad"
.AddItem "Chris Gayle"
End With
Case Is = 1
With ComboBox2
.AddItem "Neymar Jr."
.AddItem "Lionel Messi"
.AddItem "Cristiano Ronaldo"
.AddItem "Roberto Carlos"
.AddItem "Diego Maradona"
.AddItem "Pele"
End With
End Select
End Sub
Here, at first, we have defined the subroutine. Then, three items using a combo box were added. After that, we input the Cricket and Football player names separately as Case 0 and Case 1.
Read More: Create ComboBox with RowSource in Excel VBA (2 Easy Ways)
Step 3: Final Result
This is the final step of this procedure. I will show you the final result here. Follow the procedure one by one.
- After running the code, it will show the output like the picture given below for combo box 1.
- After that, the second combo bow will also show the cricket players’ names.
- Then, you can select any name from the list.
- As a result, this command will show you the category of the sport as well as the player name of that particular sport.
This is how you can cascade combo boxes in Excel VBA userform.
Read More: How to Use ComboBox to Get Selected Item Utilizing VBA
Similar Readings
- Insert ComboBox with Listindex in Excel VBA (with Easy Steps)
- How to Use VBA to Populate ComboBox List from Range in Excel
- How to Select First Item from ComboBox Using VBA in Excel
- How to Use ListFillRange Property of ComboBox in Excel
How to Select Data in a Cell from Dependent Drop-Down List in Excel
Here, in this portion of this article, I will show you how to select data in a cell from a dependent dropdown list in Excel. I will use data validation here. This data validation will make a drop-down option from a list. I will use a new dataset here. The dataset I am going to use here is shown below. The dataset has three columns. I will use column C two implement the dropdown list. The list will be made from the country names of column E.
Steps:
- After selecting the C5 cell, go to the Data tab in your toolbar.
- Then, select the number 2 icon mentioned in the image.
- After that, select the Data Validation
- Then, the following window will appear.
- Select the List option then.
- Then, select the Source option.
- Then, select the list range from E5 to E9.
- Therefore, the drop-down box will appear in cell C5.
- After that, copy down the command from C5 to C10.
Read More: Create ListBox for Multiple Columns in Excel VBA (2 Easy Ways)
Conclusion
In this article, I have tried to explain the procedures of cascading combo boxes in Excel VBA UserForm. I hope, you have learned something new from this article. Now, extend your skill by following the steps of these methods. You will find such interesting blogs on our website Exceldemy.com. I hope you have enjoyed the whole tutorial. If you have any queries, feel free to ask me in the comment section. Don’t forget to give us your feedback.
