Dynamic Cascading Lists in Excel

Creating cascading dynamic combo boxes (combo boxes which are linked) in Excel is a very handy tool. It can be done with or without VBA.  Combo boxes which are related allow the user to isolate only those values in the second combo box which is related to the selection in the first.  The relationship between the combo boxes becomes more complex the more combo boxes being used.  In the following example in VBA is used to add relevant items to each of the combo boxes.  Each combo box will contain information based on the previous combo box.  The following uses VBA to link each of the combo boxes and here is the code which achieves the task. If VBA is not your cup of tea there is an article describing 2 Cascading Lists.

You can see from the above table on the left with the 3 cascading combo boxes on the right. The first combo box contains the unique Regions. The second Combo Box contains the State which relates to the first selection and the city will relate to the state chosen. So in the example above only Sacramento, Los Angeles, San Diego and Fresno are shown in Combo Box 3 when the State of California and Region The West is chosen.

The attached procedure which produces the above result.

Private Sub ComboBox1_Change()
ComboBox2.ListIndex=-1
ComboBox3.ListIndex=-1
If ComboBox1.ListIndex > -1 Then
ComboBox2.List=Split(Cmbo(1), ",")
End if
End Sub

Private Sub ComboBox2_Change()
If ComboBox2.ListIndex > -1 Then
ComboBox3.List=Split(Cmbo(2), ",")
End If
End Sub

Function Cmbo(j)
Dim n As Integer
Dim i As Integer
Dim ar As Variant
Dim str As String


ar=Range("A10").CurrentRegion

For i=1 To UBound(ar)
For n=1 To j
If ar(i, n) <> Sheet1.OLEObjects("ComboBox" & n).Object.Value Then Exit For
Next n
If n=j + 1 And InStr(str & ",", "," & ar(i, n) & ",")=0 Then
str=str & "," & ar(i, n)
End If
Next

Cmbo=Mid(str, 2)
End Function

The file outlines the cascading combo boxes method and has the example shown above.