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

Multiple Dependent Drop-Down List Excel VBA

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.

What are Dependent Drop-Down Lists in ExcelWhat are Dependent Drop-Down Lists in Excel

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.

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.

How to Make Multiple Selections in a Drop-Down List in Excel VBA

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

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

Step 2: Select Module from Insert option

Select Module from Insert option

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

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

Now select multiple names in the Project Member column

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

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.

Create Multiple Dependent Drop-Down List in Excel VBA

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

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

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:

our full code will be like this:

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

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

Then the associated items will be available on the Food column

Step 5: The final output will be like this:

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.

Clearing Multiple Dependent Drop-Down List in Excel VBA

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

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

Clearing Multiple Dependent Drop-Down List in Excel VBA

Second

Clearing Multiple Dependent Drop-Down List in Excel VBA

Final Output

Clearing Multiple Dependent Drop-Down List in 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 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.

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo