How to Create Multiple Dependent Drop Down Lists with Excel VBA – 3 Methods

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 
			

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

• defines the subroutine:

Private Sub UserForm_Initialize()

• adds three items using a combo box:

With ComboBox1
.AddItem "Animals_Name"
AddItem "Sports_Type"
AddItem "Food_Item"
End With

• define a subroutine:

Private Sub ComboBox1_Change()

• selects three cases for the three items.
• In the first case, inputs the items in the animals category.

 Case Is = 0
        With ComboBox2
            .AddItem "Tiger"
            .AddItem "Lion"
            .AddItem "Kangaroo"
        End With

• In the second case, inputs the items in the sports category.

    Case Is = 1
        With ComboBox2
            .AddItem "Football"
            .AddItem "Cricket"
            .AddItem "Badminton"
        End With

• In the third case, inputs the items in the food category.

    Case Is = 2
        With ComboBox2
            .AddItem "Milk"
            .AddItem "Chicken"
            .AddItem "Mutton"
        End With

• ends the macro:

End sub

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