Multiple Dependent Drop-Down List Excel VBA (3 Ways)

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 relation between two drop-down lists. We generally create this using different formulas or 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 various ways to create multiple drop-down lists using VBA code in Excel.

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 then we call them dependent drop-down lists in Excel. The below picture represents a clear concept about the dependent drop-down list.

As we can see here two drop-down lists Category and Food are fully two dependent drop-down lists. Depending on the category selection we define the list of the food here. Thus, how multiple cascading drop-down lists work.

Table of Contents hide

3 Ways to Create Multiple Dependent Drop-Down List Excel VBA

2. Create Multiple Dependent Drop-Down List in Excel VBA

3. Clearing Multiple Dependent Drop-Down List in Excel VBA

Things to Remember

Conclusion

3 Ways to Create Multiple Dependent Drop-Down List Excel VBA

1. How to Make Multiple Selections in a Drop-Down List in 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.

Step 1: Go to the Developer tab and open Visual Basic (Shortcut Alt + F11)

Step 2: Select Module from Insert option

Step 3: Now write the following code in the VBA console

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim Old_value As String
 Dim New_value As String
 Application.EnableEvents = True
 On Error GoTo Exitsub
 If Not Intersect(Target, Range("C4:C11")) Is Nothing Then
   If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
      GoTo Exitsub
   Else: If Target.Value = "" Then GoTo Exitsub Else
      Application.EnableEvents = False
      New_value = Target.Value
      Application.Undo
      Old_value = Target.Value
         If Old_value = "" Then
            Target.Value = New_value
         Else
            If InStr(1, Old_value, New_value) = 0 Then
               Target.Value = Old_value & ", " & New_value
         Else:
            Target.Value = Old_value
         End If
      End If
   End If
 End If
 Application.EnableEvents = True
Exitsub:
 Application.EnableEvents = True
End Sub

Step 4: Now select multiple names in the Project Member column

Step 5: All the cells can make multiple selections from the drop-down list

2. Create Multiple Dependent Drop-Down List in Excel VBA

Let’s have a dataset of different category foods like vegetables, fruits, and dairy products. Now we want to search food items according to their category. Like if we choose the category as Fruits then in the Food column the available items should be Raspberry, Apricot, Peach, Mango. So, the food items should be available according to the category. There is a dependency between Category and Food.

Step 1: Open the VBA console by following the same steps from method 1 (Step 1 and Step 2) then write the following code

Code:

For creating a vegetable drop-down list:

Sub Vegetable_List()
 
 Range("C4:C6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
 Formula1:="=Vegetable_List"
 
End Sub

For creating a fruit drop-down list:

Sub Fruit_List()
 
 Range("C4:C6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
 Formula1:="=Fruits_list"
 
End Sub

For creating a dairy products drop-down list:

Sub Dairy_List()
 
 Range("C4:C6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
 Formula1:="=Dairy_Product_List"
 
End Sub

In this portion, we are creating lists of the individual food items and saved them in a drop-down list. This list will be available in the C4:C6 range.

Step 2: Now we need to write the main function for range B4: B6

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
  Range("B4:B6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
  Formula1:="Vegetable_List,Fruits_list,Dairy_Product_List"
  If Range("B4:B6").Value = "Vegetable_List" Then
   Call Vegetable_List
  ElseIf Range("B4:B6").Value = "Fruits_list" Then
  Call Fruit_List
  ElseIf Range("B4:B6").Value = "Dairy_Product_List" Then
  Call Dairy_List
  Else
  End If

Code Explanation

Here we are creating another list named for the category in the B4:B6 range, which will contain the names of the foods category.
Then are checking the list’s values and categories them according to their items. For this IF ELSE statement is used.
If we found any matched names, then we just called the list creating function using the CallBack method. Like

If Range(“B4:B6”).Value = “Vegetable_List” Then

Call Vegetable_List

Here if the cell’s values matched with Vegetable_List text, then we will call the Vegetable_List function to create and show the vegetable list.

So, our full code will be like this:

Step 3: Now go to the worksheet and select any category from the drop-down list

Step 4: Then the associated items will be available on the Food column

Step 5: The final output will be like this:

3. Clearing Multiple Dependent Drop-Down List in 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 selection which 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 in the first dropdown. That will prevent mismatched selections.

Step 1: Open the VBA console by following the same steps from method 1 (Step 1 and Step 2) then write the following code

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
 On Error Resume Next
If Target.Column = 2 Then
  If Target.Validation.Type = 3 Then
   Application.EnableEvents = False
   Target.Offset(0, 1).ClearContents
  End If
End If

exitHandler:
  Application.EnableEvents = True
  Exit Sub
End Sub

Step 2: Now select any items from the Food column and try to select any different category from the Category and see what happen

First

Second

Final Output

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 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 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 used functions. If you have any other method of achieving this, then please feel free to share it with us.