Video: Dependent Combo Boxes - Excel Data Entry

Make it easy to enter valid data in an Excel workbook. Choose “Fastener” in one combo box drop down. The next combo box shows a list of fastener parts, with part ID and part name.

This video shows how to use this simple data entry tool, and you’ll get a peek behind the scenes, on how it works.

Video Timeline

Here’s the video timeline, and the full video transcript is at the end of this post:

0:00 UserForm Demo
0:53 Lookup Lists Sheet
1:25 See How Macro Works
1:48 Named Range for Parts List
2:02 UserForm Macro Code

Dependent Combo Box Drop Downs

In the screen shot below, you can see the Excel UserForm from my video.

From the Part Type combo box drop down list, I selected Fastener
That affects the Part ID combo box, which is dependent on the Part Type selection
Instead of showing all the parts, the Part ID drop down only shows a list of Fastener parts.
When you select a part, only its Part ID is stored in the combo box

That makes it quick and easy to enter data.

Add to Parts Inventory

After you fill in the Parts Inventory boxes, click the Add This Part button.

That button runs Excel VBA code, which puts your data into a worksheet Excel table, for storage.

Later, you can filter that table, to find specific entries. Or, build a pivot table from the data, to get an inventory summary.

Get the Sample File

To learn more about Excel UserForms, and dependent combo boxes, go to the Excel UserForm Dependent Combo Boxes page on my Contextures website, and download the sample file.

__________________

Video Transcript: Excel UserForm With Dependent Combo Boxes

Custom Tab on Excel Ribbon

This workbook is set up so you can enter parts data, using an Excel UserForm.

On the Ribbon, there’s a special tab for this workbook, called DB macros.

If you click that, you’ll see the buttons along the Ribbon, and to open the form you click Data Entry Form.

Excel UserForm Opens

In the UserForm, you can select from the drop-down list.

It automatically fills in the current date, and a quantity of 1, and you can change those fields if you need to.

The first field is a Part Type, and the second is a Part ID

Right now, there’s nothing showing this Part ID list, because first you have to choose a part type.

There are 2 types, so we’ll select Fastener
Once I select that, then there’s a list of the fasteners
Select one of those, and its part number shows up here
Then you can select a location, and then add that part.

Add Part Order

Once you click Add Part, it clears out the top 3 combo boxes, and resets the date and quantity.

To see how this works, I’m going to go to a different sheet in this workbook, which is Lookup Lists.

See Lists Sheet

And here’s a list of all the parts with their ID, which category they’re in, and then a
description of the part.

We also have lists for the location. That’s another combo box, and the categories — we’ve got two categories.

Over here, there’s a setup for a filter. Whenever you select a part category, that name goes into this cell, and it’s a criteria range for an Advanced filter.

See How Macro Works

I’ll reopen the data entry form.

When I select a part type, fastener, nothing happens right away.
But when I leave that combo box, I’ll click in Part ID.
Now you can see that it’s put fastener into the criteria range
It ran an advanced filter, and gave me a list of all the fastener parts.

I’ll click the part ID, and I can now see that list that’s on the worksheet.

I’m going to close the form again, and this works with names.

Named Range for Parts List

Whenever it creates this list, it gives it a name. You can see here: PartSelList

That is used for the combo box for the parts

To see the code, we can go into Visual Basic, and here’s the UserForm

UserForm Macro Code

The part type, if I double click on that, you can see that after the update, this code runs.

It clears out any existing value in the part drop down
Then it sets the row source, which is that named range
It sets it to nothing, so that combo part won’t have any items in it
Then it puts a current category value into the criteria range, runs the filter, and it redefines that list
So if it’s longer or shorter, it adjusts that, and then it sets the part combo box to have
that range as its source.