Dynamic dependent Excel drop-downs using VBA

Dependent drop-downs allow the options in a drop-down to change based on the value of another drop-down. For Example. 

If the user selects Karnataka, only cities in Karnataka should be in his city drop-down. If he selects, Tamil Nadu, only cities in Tamil Nadu should be populated in the city drop-down. 

Since the drop-down has to be populated dynamically, VBA should be automatically triggered every time a value in either Column A or Column B is changed. The drop-down should include new rows that are added to Column A and B. To enable this trigger, we should write a VBA for the sheet. To do this, click on the “Visual Basic” icon in the Developer tab. 

Open the VB Editor corresponding to the sheet. Since the columns and drop-down are in Sheet1, double click Sheet1, to open the VB. 

The Sub Worksheet_Change(ByVal Target As Range) will be triggered every time a cell in Sheet1 is changed. To proceed when only a cell in Column A is changed, we can use the Target Address. In the WorkSheet_Change function, Target.Address gives the address of the cell that has been changed(eg. $B$8). To extract the column value 

Dim columnName
columnName = Split(Target.Address, "$")(1)
If columnName = "A" Then
   'Prepare state dropdown

End If

To get the last row in Column, “A”, we can use 

lastRow = Cells(Rows.Count, "A").End(xlUp).Row

We can directly use Cells function and Range function without mentioning the Workbook/ Sheet name, since the code is written in Sheet VB. To check for unique values, we can use the VBA Dictionary object. 

Dim dict As Scripting.Dictionary

Set dict = CreateObject("Scripting.Dictionary")

To get the VBA dictionary object, we need to enable Scripting in VB Editor-> Tools-> References-> Enable Microsoft Scripting Runtime

Dict.Exists returns true if the Key value exists in the dictionary and false if it does not. We can use the State Name as the Key. To add unique state names as key values into the dictionary dict, we use. (rowNumber starts from 2 to exclude title names)

For rowNumber = 2 To lastRow
  If Not dict.Exists(Cells(rowNumber, 1).Value) Then
    dict.Add Cells(rowNumber, 1).Value, ""
  End If
Next

To set Excel validation for State dropdown, we need to use Validation.Add function. To add validation to Cell D2, 

Range("D2").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="Karnataka, Tamil Nadu"

The Formula1 parameter needs the comma separated list of all states. To make this list dynamic, we use the dict.Keys to iterate over all unique State Names in dict.

stateList = ""
  For Each stateName In dict.Keys
    If stateList = "" Then
      stateList = stateList & stateName
    Else
      stateList = stateList & "," & stateName
    End If
  Next
Range("D2").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=stateList

Before adding validations, we need to remove existing validations using 

Range(“D2”).Validation.Delete

We have populated the state drop-down. We need to populate the city drop-down once the state drop-down has been changed. In the trigger function, we can write 

If Target.Address = "$B$2" Then
  ‘Populate City List

End If

To populate the city list, we need to iterate through each row of Column B. If the city in column B falls in the selected state(Value in D2= Value in Column A), then the city has to be added to the list.

lastRow = Cells(Rows.Count, "B").End(xlUp).Row
cityList = ""
For rowNumber = 2 To lastRow
  If (Cells(rowNumber, 1) = Range("D2").Value) Then
    If cityList = "" Then
      cityList = cityList & Cells(rowNumber, 2)
    Else
      cityList = cityList & "," & Cells(rowNumber, 2)
    End If
  End If
Next
Range("E2").Validation.Delete
Range("E2").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=cityList

This makes the city dropdown populate everytime state value is changed. Please find the full code below.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim columnName, lastRow, rowNumber, stateList, cityList
  Dim dict As Scripting.Dictionary
  columnName = Split(Target.Address, "$")(1)
  Set dict = CreateObject("Scripting.Dictionary")
  If columnName = "A" Then
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    For rowNumber = 2 To lastRow
      If Not dict.Exists(Cells(rowNumber, 1).Value) Then
        dict.Add Cells(rowNumber, 1).Value, ""
      End If
    Next
    stateList = ""
    For Each stateName In dict.Keys
      If stateList = "" Then
        stateList = stateList & stateName
      Else
        stateList = stateList & "," & stateName
      End If
    Next
    Range("D2").Validation.Delete
    Range("D2").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=stateList
  End If
  If Target.Address = "$D$2" Then
    Range("E2").Value = ""
    lastRow = Cells(Rows.Count, "B").End(xlUp).Row
    cityList = ""
    For rowNumber = 2 To lastRow
      If (Cells(rowNumber, 1) = Range("D2").Value) Then
        If cityList = "" Then
          cityList = cityList & Cells(rowNumber, 2)
        Else
          cityList = cityList & "," & Cells(rowNumber, 2)
        End If
      End If
    Next
    Range("E2").Validation.Delete
    Range("E2").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=cityList
  End If
End Sub

Please let me know if any difficulty