How can we create multiple dependent drop down list in Excel using VBA

Question

I require two drop down list which are dependent. In VBA, I have tried the creation of drop down for single list but I'm unable to make it dependent. The drop down list is like

Contents of First drop down list

dd1
dd2

dd4
dd5
dd6

the corresponding list2 is

for dd1

ddd1
ddd2
ddd3

for dd2

ddd4

ddd6

like wise.

I have done with the code

With Range("D1").Validation
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:=TempList
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
        End If

There mustn't be any spaces/empty cells in the list.

I have also tried this

https://siddharthrout.wordpress.com/2011/07/29/excel-data-validationcreate-dynamic-dependent-lists-vba/

But the above code supports for a single cell drop down. I need whole cells in column as drop down.

Or is there any method to create directly the dependent drop downs using formula with elimination of blank cells in both the columns.

Thanks in advance

MatthewD MatthewD · Accepted Answer · 2015-07-24T12:43:30

You will have to store the relationships for those values somewhere. Then when one is selected populate the other. Here is an example if the values are stored in Columns A & B.

A   B
--- ----
dd1 ddd1
dd1 ddd2
dd1 ddd3
dd2 ddd4
dd2 ddd6

On the change event of the first list, look for what to put in the second list based on what was selected in the first list.

Private Sub ComboBox1_Change()
Dim lRow As Long

'Clear out the second list
ComboBox2.Clear

lRow = 1
Do While lRow <= ws.UsedRange.Rows.Count

    If ws.Range("A" & lRow).Value = ComboBox1.Text Then
        'Column A matches what was selected in the first list so add the value in columnB to the second list.
        ComboBox2.AddItem ws.Range("B" & lRow).Value
    End If

lRow = lRow + 1
Loop

End Sub

If you data is stored somewhere else like a database,

Private Sub ComboBox1_Change()
Dim strSQL as string

    'Clear out the second list
    ComboBox2.Clear

    strSQL = "Select fieldname2 from tablename where fieldname1 = '" & ComboBox1.Text & "'"

    'Put the results of the query into combobox2

End sub

How can we create multiple dependent drop down list in Excel using VBA

1 Answers