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
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.
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
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.
In columns A:C, there is a parts list, with an ID number, the part type (PartCat) and the part name.
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.
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.
To see the data entry UserForm:
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.
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.
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 = ""
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
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
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"
Finally, the code updates the Row Source for the Part ID combo box.
Me.cboPart.RowSource = "PartSelCatList"
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.
Last updated: June 15, 2017 1:42 PM
Contextures RSS Feed