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.

Table of Contents hide

Download Practice Workbook

What Are Dependent Drop Down Lists in Excel?

3 Effective Ways to Make a Multiple Dependent Drop Down List with Excel VBA

Method 1: Entering Multiple Data in a Cell from Dependent Drop Down List with Excel VBA

Method 2: Creating a Multiple Dependent Drop Down List in UserForm with Excel VBA

Method 3: Clearing Mismatched Values from a Multiple Dependent Drop Down List with Excel VBA

Things to Remember

Conclusion

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it by yourself.

Creating Dependent Drop Down List.xlsm

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 the certain category.

3 Effective Ways to Make a Multiple Dependent Drop Down List with Excel VBA

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

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

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.

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.

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

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.

Read more: How to Remove Drop Down List in Excel

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.

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.