Home > Macros >UserForms >Dependent Excel UserForm Dependent Comboboxes To make it easier to enter data in a workbook, show a UserForm with drop down lists in combo boxes. This example adds a combo box in which the list depends on the selection made in another combo box

Introduction to Dependent Combo Boxes

To create a UserForm requires some programming, and there are setup instructions here: Create an Excel UserForm with Combo Boxes.

In this example, you will choose a Part Type first. The Part ID combo box list shows the parts that match the part type that you selected -- in other words, Part ID is dependent on the selection in Part Type. Watch the video below, to see how it works.

Video: Create Dependent Combo Boxes

To see the steps for creating a dependent combo boxes in a UserForm, watch this short video tutorial. The sample code is below the video, and you can also download the sample file.

Dependent Combo Box Code

The following code is in the UserForm, on the Part Type combobox, and you can see it in the sample file.

Private Sub cboType_AfterUpdate()
On Error Resume Next
Dim ws As Worksheet
Dim cPart As Range
Set ws = Worksheets("LookupLists")

Me.cboPart.Value = ""
Me.cboPart.RowSource = ""

With ws
   .Range("CritPartCat").Cells(2, 1).Value _
      = Me.cboType.Value
   .Columns("A:C").AdvancedFilter _
      Action:=xlFilterCopy, _
      CriteriaRange:=.Range("CritPartCat"), _
      CopyToRange:=.Range("ExtPartDesc"), _
      Unique:=False
End With

'redefine the static named range
ThisWorkbook.Names.Add Name:="PartSelList", _
  RefersTo:="=" & ws.Name & "!" & _
  ws.Range("PartSelCatList").Address

Me.cboPart.RowSource = "PartSelCatList"

End Sub

Workbook Setup

When the sample file opens, a custom tab -- Db Macros -- is added to the Excel Ribbon. Use those buttons to show or hide parts of the workbook, and to show the data entry form.

• NOTE: To see how to set up this custom tab on the Ribbon, go to the Excel Ribbon - Getting Started page.

Click the Show Lists button on the Ribbon's Db Macros tab, to see the sheet where the Parts list is stored.

Worksheet Lists

In columns A:C, there is a parts list, with an ID number, the part type (PartCat) and the part name.

Advanced Filter Setup

The macro will run an Advanced Filter to create a parts list. There is a criteria range for the Advanced Filter in cells K1:K2.

• The heading in cell K1 matches the heading in cell B1.
• The macro will put the selected part type in cell K2.

When the macro runs, it runs an Advanced Filter that updates the parts list In columns M:N. The updated list includes all the parts that match the part type entered in criteria cell K2.

See the UserForm

To see the data entry UserForm:

• In Excel, press Alt+F11
• In the Project Explorer, find the PartLocDbComboRibbonDepend file
• Click the plus sign at the left of the Forms folder
• Double-click the frmParts UserForm, to show it in the Code window.

Combo Box Code

To make the dependent combo boxes work, code is added to the Part Type combo box's AfterUpdate event.

To see the code, right-click on the Part ID combo box, and click View Code.

How the Code Works

When you make a selection in the Part Type combo box (cboType), you are updating that combo box. When that happens, the AfterUpdate code runs automatically.

Clear Previous Selection

First, the code identifies the sheet where the Parts list is stored.

Set ws = Worksheets("LookupLists")

Next, it clears out the Part ID combo box (cboPart), in case a selection was made previously. This will prevent an incorrect match of Part and Part Type.

Me.cboPart.Value = ""

The Row Source property for the Part ID combo box has the name of the Parts list to use, on the LookupLists sheet. The code clears that Part ID row source, by changing it to an empty string.

Me.cboPart.RowSource = ""

Update the Criteria

Next, the code puts the selected Part Type into the criteria range on the LookupLists sheet. The Advanced Filter will only list parts with that part type.

With ws
   .Range("CritPartCat").Cells(2, 1).Value _
      = Me.cboType.Value

Create New Parts List

Then the Advanced Filter runs, and creates a list of parts with that part type.

   .Columns("A:C").AdvancedFilter _
      Action:=xlFilterCopy, _
      CriteriaRange:=.Range("CritPartCat"), _
      CopyToRange:=.Range("ExtPartDesc"), _
      Unique:=False

Update the Parts List

Next, the code redefines the named range PartSelList, based on the filtered list.

ThisWorkbook.Names.Add Name:="PartSelList", _
  RefersTo:="=" & ws.Name & "!" & _
  ws.Range("PartSelCatList").Address

Me.cboPart.RowSource = "PartSelCatList"

Update the Part ID Combo Box

Finally, the code updates the Row Source for the Part ID combo box.

Me.cboPart.RowSource = "PartSelCatList"

Get the Sample File

To see how the user form works, you can get the sample Excel UserForm with Combo Boxes file.

The zipped file is in xlsm format (Excel 2007 and later), and contains macros. Enable macros to test the code.

More Tutorials

Basic Excel UserForm

Create an Excel UserForm with Combo Boxes

Excel Ribbon Custom Tab

UserForm with Help Pages