Creating a multiple-dependent drop-down has always been a challenge in MS Excel. We need this kind of drop-down list when there is a dependency or relationship between two drop-down lists. We generally create this using different formulas or by changing appropriate options in Excel. However, there are some easy ways to create multiple drop-down lists using Excel VBA code. In this article, I will show you various ways to create multiple dependent drop-down list by using Excel VBA.
What Are Dependent Drop Down Lists in Excel?
Before going to the main process, let’s figure out what a dependent drop-down list is in Excel. When there is a dependency between two or more drop-down lists, we call them dependent drop-down lists in Excel. When we change the value of one drop-down list, it will show the values in another drop-down list under a certain category.
How to Make Multiple Dependent Drop Down List with Excel VBA: 3 Effective Ways
In the following sections, we will extract values from multiple dependent drop-down lists using Excel VBA. Later on, we create multiple dependent drop-down lists in a UserForm from Macro. Finally, we will clear the mismatched values under specific categories As we can see in the following data set, we have some people’s names, and we will assign them to different projects with the assistance of a drop-down list. Additionally, we will use another data set depending on the category selection to get the list of foods. Thus, multiple cascading drop-down lists will work.
Method 1: Entering Multiple Data in a Cell from Dependent Drop Down List with Excel VBA
Let’s say we have two lists named Project Name and Project Members. For each project, we will assign one or multiple members using a drop-down list.
- Firstly, go to the Developer tab and open Visual Basic (Shortcut Alt + F11)
- Then, go to the respective worksheet from the respective VBAProject menu.
- After that, write the following code in the VBA console.
Private Sub Worksheet_Change(ByVal Target As Range) 'Declare variable Dim xOld_Val As String Dim xNew_Val As String 'Enable event Application.EnableEvents = True On Error GoTo Exitsub 'Target the range If Not Intersect(Target, Range("C5:C12")) Is Nothing Then If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then GoTo Exitsub Else: If Target.Value = "" Then GoTo Exitsub Else 'Enable event Application.EnableEvents = False 'Specify target value into variable xNew_Val = Target.Value Application.Undo xOld_Val = Target.Value 'Apply If else statement If xOld_Val = "" Then Target.Value = xNew_Val Else If InStr(1, xOld_Val, xNew_Val) = 0 Then Target.Value = xOld_Val & ", " & xNew_Val Else: Target.Value = xOld_Val End If End If End If End If Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub
- Firstly, we will define our subroutine as,
Private Sub Worksheet_Change(ByVal Target As Range)
- Secondly, we declare our variables as,
Dim xOld_Val As String Dim xNew_Val As String
- Thirdly, we specify the targeted cell range validation from the worksheet as,
If Not Intersect(Target, Range("C5:C12")) Is Nothing Then If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then GoTo Exitsub Else: If Target.Value = "" Then GoTo Exitsub Else
- After that, we will specify the target value into a variable as,
xNew_Val = Target.Value Application.Undo xOld_Val = Target.Value
- Then, we apply the If Else statement as,
If xOld_Val = "" Then Target.Value = xNew_Val Else If InStr(1, xOld_Val, xNew_Val) = 0 Then Target.Value = xOld_Val & ", " & xNew_Val Else: Target.Value = xOld_Val
- Finally, we will end the VBA macro as,
- Afterward, select multiple names in the Project Member column.
- Finally, all the cells can make multiple selections from the drop-down list.
Method 2: Creating a Multiple Dependent Drop Down List in UserForm with Excel VBA
We’ll look at an Excel VBA application that generates a UserForm with dependent combo boxes for creating multiple dependent drop-down lists.
- Firstly, go to the Insert option.
- Then, click on the UserForm option.
- Therefore, the Userform that we’re going to build looks like this in the below image.
- Besides, we will add combo boxes and command buttons.
- Here, choose position 3 to add one combo box on the left and the second combo box on the right side.
- Then we also add the command button by clicking position 4 in the below image.
- So, please right-click on the UserForm to view the code we have created.
- Then, paste the following VBA code here and press F5 to run the program.
Private Sub UserForm_Initialize() With ComboBox1 .AddItem "Animals_Name" .AddItem "Sports_Type" .AddItem "Food_Item" 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 "Tiger" .AddItem "Lion" .AddItem "Kangaroo" End With Case Is = 1 With ComboBox2 .AddItem "Football" .AddItem "Cricket" .AddItem "Badminton" End With Case Is = 2 With ComboBox2 .AddItem "Milk" .AddItem "Chicken" .AddItem "Mutton" End With End Select End Sub
- Firstly, we will define our subroutine as,
Private Sub UserForm_Initialize()
- Then, we will add three items using a combo box as,
With ComboBox1 .AddItem "Animals_Name" AddItem "Sports_Type" AddItem "Food_Item" End With
- Again, will define our subroutine as,
Private Sub ComboBox1_Change()
- Then, we will select three cases for all three items as we mentioned earlier.
- In the first case, we will input the items of the animals’ category.
Case Is = 0 With ComboBox2 .AddItem "Tiger" .AddItem "Lion" .AddItem "Kangaroo" End With
- In the second case, we will input the items of the sports category.
Case Is = 1 With ComboBox2 .AddItem "Football" .AddItem "Cricket" .AddItem "Badminton" End With
- In the third case, we will input the items of the food category.
Case Is = 2 With ComboBox2 .AddItem "Milk" .AddItem "Chicken" .AddItem "Mutton" End With
- Finally, we will end the macro as,
- Therefore, you will see the given UserForm window.
- Then, we will select Animals_Name from the first combo box.
- As a result, we can see the list of animals from the second combo box.
Method 3: Clearing Mismatched Values from a Multiple Dependent Drop Down List with Excel VBA
In the previous section, we have seen only how we can get the related matched list in Excel. But sometimes there could be some mismatched selections that will not be removed automatically. We could formula to prevent this kind of problem. Another option is to use a macro to clear the dependent cell after selecting it in the first dropdown. That will prevent mismatched selections.
- Firstly, open the VBA console by following the same steps from method 1 and then write the following code.
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next 'Define target column as 3 If Target.Column = 3 Then 'Applying If condition for target validation type as 3 If Target.Validation.Type = 3 Then 'Disable event by declaring the statement as False Application.EnableEvents = False 'Move down 1 row and to the right 0 column Target.Offset(0, 1).ClearContents End If End If exitHandler: 'Enable event by declaring the statement as True Application.EnableEvents = True Exit Sub End Sub
- Applying the If condition by defining the target column as 3.
If Target.Column = 3 Then
- Applying the If the condition for target validation type as 3.
If Target.Validation.Type = 3 Then
- Disable the event by declaring the statement as False.
Application.EnableEvents = False
- Move down 1 row and to the right 0 column.
- Enable the event by declaring the statement as True.
Application.EnableEvents = True
- Then, select any items from the Food column and try to select any different category from the Category and see what happens.
- Lastly, you will see that corn is not visible because corn is not on the list of vegetables.
Things to Remember
|Common Errors||When they show|
|Unable to delete List||In the Data Validation, if the Allow is not equal to List and the Source is not correctly selected, then the drop-down list can’t be deleted, or you use VBA code to delete the list.|
|Update value problem||Generally, in the dependent drop-down lists, if there is any mismatched value, it will not update automatically. We could either use a formula or VBA code (method 3 in this article) to update the value automatically.|
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice it by yourself.
These are some ways to create or manipulate multiple dependent drop-down lists in Excel VBA. I have shown all the methods with their respective examples, but there can be many other iterations. I have also discussed the fundamentals of the functions used. If you have any other method of achieving this, then please feel free to share it with us.