Contextures

Excel UserForm With 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.

So, Part ID is dependent on the selection in Part Type. Watch the video below, to see how it works.

Excel UserForm Test

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.

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

show lists button on ribbon

Worksheet Lists

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

parts list

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.

criteria range

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.

list of parts for selected part type

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.

data entry userform

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.

view the Part Id combo box 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"

Download the Sample File

To see how the user form works, you can download 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.

Excel UserForms for Data Entry

More Tutorials

Excel Data Entry UserForms Kit

Basic Excel UserForm

Create an Excel UserForm with Combo Boxes

Search Contextures Sites

 

Excel Tools Add-in

Free Pivot Table Tools

 

Pivot Power Premium

 

Excel Data Entry Popup List

 

 

 

Excel UserForms for Data Entry

 

Last updated: August 19, 2016 11:39 AM
Contextures RSS Feed