In this example, we will create a userform that contains dependent combo boxes and a textbox. We will use this userform to enter the data into cells as fastly.
The UserForm will be opened automatically when any cell in "Column A" is selected.
The important point in this template ; by creating a dependency between combo boxes, selectively choices can be limited. When the user selects Supplier from first drop-down list ,as a result, Category can be selected according to supplier from second drop-down list. Later ,based on selected category ,Product can be selected from third drop-down list. Code of product is added in textbox as automatically.
- Firstly, we need to create lists of combo boxes . For this we created lists in other sheet. Heads of lists : Supplier | Category | Product| Code
- Secondly ,we need to define names for lists. When lists are subject to updates, we can use a dynamic range instead. To create a dynamic range name for the Supplier list, do the following:
1- Click Define Name in the Defined Names group on the Formulas tab. (In Excel 2003, Choose Name from the Insert menu and choose Define)
2- Name the list Supplier.
3- Enter the following formula in the Refers To control : =OFFSET(Database!$A$2;0;0;COUNTA(Database!$A:$A)-1)
4- Click OK.
We repeated steps 1 through 4 to create dynamic lists for the other three lists:
Category: =OFFSET(Database!$B$2;0;0;COUNTA(Database!$B:$B)-1)
Product: =OFFSET(Database!$C$2,0,0,COUNTA(Database!$C:$C)-1)
Code: =OFFSET(Database!$D$2,0,0,COUNTA(Database!$D:$D)-1)Later , we entered codes into VBE (Visual Basic Editor) .
