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.
Read more: How to Make a Drop Down List in Excel (Independent and Dependent)
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.
Read more: How to Create Dynamic Dependent Drop Down List in Excel
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
Read more: How to Make Multiple Selection from Drop Down List in Excel
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
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 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.
Further Readings
- How to Create Drop Down List in Multiple Columns in Excel (3 Ways)
- Excel Drop Down List Depending on Selection
- How to use IF Statement to Create a Drop-Down List in Excel
- Create a Drop Down List From Another Sheet in Excel (2 Methods)
- How to Edit Drop-Down List in Excel (4 Basic Approaches)
- VLOOKUP with Drop Down List in Excel
“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.
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”?