Demo: Excel UserForm With Dependent Combo Boxes
188.7 هزار بار بازدید -
12 سال پیش
-
In an Excel UserForm, you
In an Excel UserForm, you can add combo boxes to make data entry easier. In this example, there is a dependent combo box -- Part ID. The items shown in that combo box depend on what was selected in the Part Type combo box.
To see how it works, please watch this short video tutorial.
Visit this page for written steps, and to download the sample file:
https://www.contextures.com/exceluser...
Video Timeline:
0:00 Custom Tab on Excel Ribbon
0:17 Excel UserForm Opens
0:37 Add Part Order
0:55 See Lists Sheet
1:25 See How Macro Works
1:50 Named Range for Parts List
2:00 UserForm Macro Code
'--------------------
Video Transcript
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.
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.
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.
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.
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.
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
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.
To see how it works, please watch this short video tutorial.
Visit this page for written steps, and to download the sample file:
https://www.contextures.com/exceluser...
Video Timeline:
0:00 Custom Tab on Excel Ribbon
0:17 Excel UserForm Opens
0:37 Add Part Order
0:55 See Lists Sheet
1:25 See How Macro Works
1:50 Named Range for Parts List
2:00 UserForm Macro Code
'--------------------
Video Transcript
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.
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.
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.
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.
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.
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
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.
12 سال پیش
در تاریخ 1391/12/16 منتشر شده
است.
188,705
بـار بازدید شده