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.
Steps:
- 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,Â
End Sub- Afterward, select multiple names in the Project Member column.
- Finally, all the cells can make multiple selections from the drop-down list.
Read More: How to Use Named Range for Data Validation List with VBA in Excel
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.
Steps:
- 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
VBA BREAKDOWN
- 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,Â
End sub- 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.
Read More: Unique Values in a Drop Down List with VBA in Excel
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.
Steps:
- 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.
 Target.Offset(0, 1).ClearContents- 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.
Conclusion
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.
“2. Create Multiple Dependent Drop-Down List in Excel VBA” is not working for me. When i run macro it ask which macro to run i.e. veg, fruit or dairy. Dropdown do not create. If possible send sample file of this.
Hi AVINASH. Check your email for a sample file. I have used the INDIRECT function with the Data Validation command to make it.
Regards.
Hello,
In you second point, is it possible if I choose Vegetable_List, the right column automatically select third vegetable from the list, in this example, “Lettuce”?
I have followed the steps # 1, 2 & 3 of “2. Create Multiple Dependent Drop-Down List in Excel VBA” but I only got the dropdown list for the “Category” column and not for the second column “Food”. What would be the issue?
Thanks and regards,
[email protected]
you have three columns of values in Columns E, F & G for ‘Vegetable’, ‘Fruits’ & ‘Dairy_Product’, but nowhere in the code do you tell the code to refer to these values.
How does the code know that it should refer to the values in these tables?