Dependent ComboBox Drop Down Menus

How to create UserForm drop-down menus that change based on what was selected in another drop-down menu; these are called dependent drop-down menus.

Sections:

Add Items to the First ComboBox Menu

Add Items to the Dependent ComboBox Menu

Notes

Add Items to the First ComboBox Menu

The first ComboBox menu should have items added to it in the regular way. This is the menu that will cause the dependent menu to change.

Here, I'll show you the basic way to add items to the drop-down menu; if you want a thorough explanation, read this tutorial from us: Add Values to a ComboBox

This tutorial will use the .List array method for adding items to a ComboBox.

Go to the UserForm_Initialize() event, in the code window for the form, and add code like this:

ComboBox1.List = Array("Item 1", "Item 2", "Item 3")

The code highlighted in blue is the code for the ComboBox. It will add three items to the first drop-down menu on the form.

Add Items to the Dependent ComboBox Menu

To get dependent drop-down menus to change based on a previous selection, we must put code inside the Change event for the first ComboBox; this is the ComboBox whose selection will cause the values in the subsequent ComboBox to change.

In the VBA Project Explorer window, double-click the form so that it is visible and then double-click on the first ComboBox.

You should now see the code window and the code section for ComboBox1_Change like this:

Make sure you see ComboBox1 (or the name of your ComboBox) in the left drop-down menu at the top of the window and Change in the drop-down menu on the right.

ComboBox1 is the name of the drop-down menu; the name for yours could be different.

Put this code inside that event:

'The selection from the first drop-down menu.
UserSelection = ComboBox1.Value

'Control the output of the dependent menu.
Select Case UserSelection

    Case "Item 1"

        'Fill the dependent ComboBox.
        ComboBox2.List = Array("Apple", "Orange")

    Case "Item 2"

        'Fill the dependent ComboBox.
        ComboBox2.List = Array("Green", "Blue")

    Case "Item 3"

        'Fill the dependent ComboBox.
        ComboBox2.List = Array("Sky", "Ground")

    Case Else

        'Default option.

End Select

ComboBox1 is the first drop-down menu.

ComboBox2 is the dependent drop-down menu.

Remember, this goes inside the change event for the first ComboBox.

Item 1 is the name of a possible selection from the first menu.

Item 2 is the name of a possible selection from the first menu.

Note: add new section for each "item" that can be selected from the initial drop-down menu. Each section will follow this format:

Case "Item 3"

        'Fill the dependent ComboBox. 
        ComboBox2.List = Array("Something Here", "Another Thing Here")

More than 2 Dependent Drop Down Menus

You can have as many dependent drop-down ComboBox menus as you want. Simply follow the example above and add the code section for each ComboBox into the Change event for the previous ComboBox.

So, if you had a third drop-down menu, you would add the code from the last section into the Change event for the second ComboBox, in this case, ComboBox2. Following that, you would change each instance of ComboBox1 to ComboBox2 and change each instance of ComboBox2 to ComboBox3 (or whatever you named your third ComboBox).

Notes

Making dependent drop-down menus for UserForms in Excel is not that difficult. Once you get the general pattern down for it, you just copy/paste the code and change the names of the ComboBox's and the items in the menus.

Download the sample file for this tutorial to get working examples in Excel.