Dependent Combo Boxes on Excel VBA Userform

December 20, 2019 Code VBA, VBA Quick Training,

Dependent Combo Boxes on Excel VBA Userform

In this blog post we will look at how to create dependent combo boxes for your userforms in Excel VBA.

So the drop down options in the second combo box are dependent upon the selection made in the first combo box.

We will cover 2 ways of achieving this.

Create a Dependent Combo Box with Named Ranges

In this example we have two combo boxes on a userform.

One with a list of countries, and another with a list of cites. We would like the second list to only show the cities for the country selected in the first list.

The first combo box is named cboCountry and the second combo box named cboCity.

The spreadsheet with the data being used by these combo boxes can be seen below.

Each list has been given a defined name. We can then reference that name within our VBA code in a similar way to my dependent data validation list tutorial.

The VBA code below has been used in the combo box change event for cboCountry, so that whenever a selection is made in that list, the code is triggered.

This code uses the Select Case conditional construct. If you are not familiar with this technique, check out my Select Case tutorial.

Private Sub cboCountry_Change()

Select Case cboCountry.Value
 Case Is = "UK"
 cboCity.RowSource = "uk"
 Case Is = "Spain"
 cboCity.RowSource = "spain"
 Case Is = "New Zealand"
 cboCity.RowSource = "new_zealand"
 Case Is = "Italy"
 cboCity.RowSource = "italy"
 Case Is = "Netherlands"
 cboCity.RowSource = "netherlands"
 Case Is = "Russia"
 cboCity.RowSource = "russia"
End Select

End Sub

The Select Case statement makes it easy for us to test the combo box value multiple times, and provide the correct row source for the second combo box.

Watch the Video – Dependent Combo Boxes

Dependent Combo Boxes with the Advanced Filter

In this second example we will link two combo boxes together so that when a country is selected from the first combo box, a list of customers in that country is shown in the second combo box.

The userform with the two combo boxes looks like this.

The first combo box is named cboCountry and the second combo box is named cboCustomer.

In this example, the spreadsheet data looks like below.

In column A we have the list of countries used by the cboCountry combo box. And in columns C and D, the countries with the customers we have in those regions.

We do not have named ranges like in example 1. So this time when a selection is made in combo box 1, we will perform a search for the customers in that country from column C and D.

So in this example the plan is to use the fantastic Advanced Filter of Excel.

The code below is used on the change event for cboCountry combo box.

Private Sub cboCountry_Change()

Dim r As Integer

r = 2

Range("F2").Value = cboCountry.Value

Columns("C:D").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
 "F1:F2"), CopyToRange:=Range("H1:I1"), Unique:=False

cboCustomer.Clear

Do Until Cells(r, 9).Value = ""

cboCustomer.AddItem Cells(r, 9).Value
 r = r + 1

Loop

End Sub

When the selection is made in cboCountry. The value is written to cell F2.

The Advanced Filter is then perform using range F1:F2 as its criteria range. The results of the filter are produced in columns H:I.

The items for the second combo box are then created by using a Do loop and the AddItems method for the combo box.

Watch the Video – Dependent Combo Boxes with Advanced Filter