How to Make Multiple Dependent Drop Down List with Excel VBA

Get FREE Advanced Excel Exercises with Solutions!

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.

Entering Multiple Data in a Cell from a Dependent Drop-Down List with Excel VBA

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 
			

VBA Breakdown
  • 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
End Sub
  • Afterward, select multiple names in the Project Member column.

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.

Creating a Multiple Dependent Drop-Down List in UserForm with Excel VBA

  • 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.

Clearing Mismatched Values from a Multiple Dependent Drop-Down List with Excel VBA

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 
			

Clearing Mismatched Values from a Multiple Dependent Drop-Down List with Excel VBA

VBA Breakdown
  • 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.

Clearing Mismatched Values from a Multiple Dependent Drop-Down List with Excel VBA

Read More: Default Value in Data Validation List with Excel VBA


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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdullah Al Murad
Md. Abdullah Al Murad

Md. Abdullah Al Murad is a computer engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in C, C++, Python, JavaScript, HTML, SQL, MySQL, PostgreSQL, Microsoft Office, and VBA and goes beyond the basics. He holds a B.Sc. in Computer Science and Engineering from American International University-Bangladesh. He has transitioned into the role of a content developer. Beyond his work, find... Read Full Bio

5 Comments
  1. “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.

  2. 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”?

  3. 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]

  4. 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?

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo