Dependent ComboBox Drop Down Menus

Add to Favorites
Author: don | Edits: don

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.

f1ded65c22b9f180ce1db3df48ebb9c1.png

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")

38d025c2c24e17710b384ead2274934b.png

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.

bb7735a0c263ca83a7bfddb918edec10.png

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

112d7da4c0cb0b829f624784e69eaf98.jpg

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")

0a22269e8d2b29d0eb102cc5c30cf957.png

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.

Question? Ask it in our Excel Forum


Downloadable Files: Excel File

Similar Content on TeachExcel
Multi-Column ComboBox Drop Down Menus in Forms
Tutorial: Multiple columns of data within a UserForm ComboBox drop-down menu in Excel. I'll show you...
Add a Drop Down Menu to a Cell in Excel - Data Validation Macro
Macro: Add a drop down menu or list to a cell in Excel with this free Excel macro. This is a grea...
Make a Drop Down List Menu in a Cell in Excel
Tutorial: I'll show you how to make a drop down list menu in a cell in Excel. This allows you to h...
UserForm Events
Tutorial: Explanation of UserForm Events in Excel. This includes what they are, how to use them, and...
Getting Data from a UserForm
Tutorial: How to get data from a UserForm in Excel, including from: text inputs (TextBox), list boxe...
Put Data into a UserForm
Tutorial: How to take data from Excel and put it into a UserForm. This is useful when you use a form...
Tutorial Details
Downloadable Files: Excel File
Similar Content
Multi-Column ComboBox Drop Down Menus in Forms
Tutorial: Multiple columns of data within a UserForm ComboBox drop-down menu in Excel. I'll show you...
Add a Drop Down Menu to a Cell in Excel - Data Validation Macro
Macro: Add a drop down menu or list to a cell in Excel with this free Excel macro. This is a grea...
Make a Drop Down List Menu in a Cell in Excel
Tutorial: I'll show you how to make a drop down list menu in a cell in Excel. This allows you to h...
Excel Forum