Multi-Level Dependent Dynamic Drop-down Lists in Excel VBA

How to Create a Multi-level dependent dynamic Drop-down lists using a single user defined function in VBA?

Whenever we develop any automated tools/trackers in Excel VBA, we may face some scenarios where we need to create multilevel cascading drop-down fields. To solve this programming requirement, we create or use multiple tables or looping statement for dependent drop-down. All these steps are complex and are prone to errors. To solve this issue, we have developed a Custom VBA function which can be utilized in a very simple way and that function will create auto expanding, multi-level truly dynamic dependent drop-down lists for combo box, list box or cell base lists. You just need to pass some parameters in that function and it’s done. Function will manage all the activities itself.

Let’s create an Excel file and use the function to understand how it works. Follow the below steps:

  1. Create a new Excel file and add three worksheets in that

2. Save this file with the name ‘Dynamic Drop Down’ having Macro enabled extension (.XLSM).

3. Now Rename all the worksheets as per our requirement.
Here, we will be using Sheet1 to keep a command button so that user can click to launch the User Form. We will utilize Sheet2 to keep the master table for drop-down and Sheet3 will be for the VBA to create dynamic drop-down lists basis on data selections by user.
You may use any name for all these sheets as per your project requirement. In this demo, I am going to rename ‘Sheet1‘ to ‘Home‘, ‘Sheet2‘ to ‘Master Sheet‘ and ‘Sheet3‘ to ‘Drop Down‘.

4. Let’s create a command button with name ‘Show My Form‘ in Home sheet.

5. In Master Sheet, we need to create a table for the drop-down fields. This table will be a simple table having all the columns which are required for the drop-down.
For example, if you have three level of drop-down fields in your user form then you will create three columns table and so on.
Here, I am going to create 4 level of drop-down fields with generic name e.g. Category, Sub Category, Item and Sub Item.
Please see the below image to understand the table format and requirement.

We will be using below master table in this demo. If you want to use this to learn then you just need to copy and paste it in your master sheet (or other sheet which is dedicated for master drop-down data).

CategorySub CategoryItemSub Item
Category-1Sub Category -1Item-1Sub Item – 1
Category-1Sub Category -1Item-1Sub Item – 2
Category-1Sub Category -1Item-1Sub Item – 3
Category-1Sub Category -1Item-1Sub Item – 4
Category-1Sub Category -1Item-1Sub Item – 5
Category-1Sub Category -1Item-1Sub Item – 6
Category-1Sub Category -1Item-1Sub Item – 7
Category-1Sub Category -1Item-1Sub Item – 8
Category-1Sub Category -1Item-1Sub Item – 9
Category-1Sub Category -1Item-1Sub Item – 10
Category-1Sub Category -1Item-2Sub Item – 11
Category-1Sub Category -1Item-2Sub Item – 12
Category-1Sub Category -1Item-2Sub Item – 13
Category-1Sub Category -1Item-2Sub Item – 14
Category-1Sub Category -1Item-2Sub Item – 15
Category-1Sub Category -1Item-2Sub Item – 16
Category-1Sub Category -1Item-2Sub Item – 17
Category-1Sub Category -1Item-2Sub Item – 18
Category-1Sub Category -1Item-2Sub Item – 19
Category-1Sub Category -1Item-2Sub Item – 20
Category-1Sub Category -2Item-3Sub Item – 21
Category-1Sub Category -2Item-3Sub Item – 22
Category-1Sub Category -2Item-3Sub Item – 23
Category-1Sub Category -2Item-3Sub Item – 24
Category-1Sub Category -2Item-3Sub Item – 25
Category-1Sub Category -2Item-3Sub Item – 26
Category-1Sub Category -2Item-3Sub Item – 27
Category-1Sub Category -2Item-3Sub Item – 28
Category-1Sub Category -2Item-3Sub Item – 29
Category-1Sub Category -2Item-3Sub Item – 30
Category-1Sub Category -2Item-4Sub Item – 31
Category-1Sub Category -2Item-4Sub Item – 32
Category-1Sub Category -2Item-4Sub Item – 33
Category-1Sub Category -2Item-4Sub Item – 34
Category-1Sub Category -2Item-4Sub Item – 35
Category-1Sub Category -2Item-4Sub Item – 36
Category-1Sub Category -2Item-4Sub Item – 37
Category-1Sub Category -2Item-4Sub Item – 38
Category-1Sub Category -2Item-4Sub Item – 39
Category-1Sub Category -2Item-4Sub Item – 40
Category-2Sub Category -3Item-5Sub Item – 41
Category-2Sub Category -3Item-5Sub Item – 42
Category-2Sub Category -3Item-5Sub Item – 43
Category-2Sub Category -3Item-5Sub Item – 44
Category-2Sub Category -3Item-5Sub Item – 45
Category-2Sub Category -3Item-5Sub Item – 46
Category-2Sub Category -3Item-5Sub Item – 47
Category-2Sub Category -3Item-5Sub Item – 48
Category-2Sub Category -3Item-5Sub Item – 49
Category-2Sub Category -3Item-5Sub Item – 50
Category-2Sub Category -3Item-6Sub Item – 51
Category-2Sub Category -3Item-6Sub Item – 52
Category-2Sub Category -3Item-6Sub Item – 53
Category-2Sub Category -3Item-6Sub Item – 54
Category-2Sub Category -3Item-6Sub Item – 55
Category-2Sub Category -3Item-6Sub Item – 56
Category-2Sub Category -3Item-6Sub Item – 57
Category-2Sub Category -3Item-6Sub Item – 58
Category-2Sub Category -3Item-6Sub Item – 59
Category-2Sub Category -3Item-6Sub Item – 60
Category-2Sub Category -4Item-7Sub Item – 61
Category-2Sub Category -4Item-7Sub Item – 62
Category-2Sub Category -4Item-7Sub Item – 63
Category-2Sub Category -4Item-7Sub Item – 64
Category-2Sub Category -4Item-7Sub Item – 65
Category-2Sub Category -4Item-7Sub Item – 66
Category-2Sub Category -4Item-7Sub Item – 67
Category-2Sub Category -4Item-7Sub Item – 68
Category-2Sub Category -4Item-7Sub Item – 69
Category-2Sub Category -4Item-7Sub Item – 70
Category-2Sub Category -4Item-8Sub Item – 71
Category-2Sub Category -4Item-8Sub Item – 72
Category-2Sub Category -4Item-8Sub Item – 73
Category-2Sub Category -4Item-8Sub Item – 74
Category-2Sub Category -4Item-8Sub Item – 75
Category-2Sub Category -4Item-8Sub Item – 76
Category-2Sub Category -4Item-8Sub Item – 77
Category-2Sub Category -4Item-8Sub Item – 78
Category-2Sub Category -4Item-8Sub Item – 79
Category-2Sub Category -4Item-8Sub Item – 80
Category-3Sub Category -5Item-9Sub Item – 81
Category-3Sub Category -5Item-9Sub Item – 82
Category-3Sub Category -5Item-9Sub Item – 83
Category-3Sub Category -5Item-9Sub Item – 84
Category-3Sub Category -5Item-9Sub Item – 85
Category-3Sub Category -5Item-9Sub Item – 86
Category-3Sub Category -5Item-9Sub Item – 87
Category-3Sub Category -5Item-9Sub Item – 88
Category-3Sub Category -5Item-9Sub Item – 89
Category-3Sub Category -5Item-9Sub Item – 90
Category-3Sub Category -5Item-10Sub Item – 91
Category-3Sub Category -5Item-10Sub Item – 92
Category-3Sub Category -5Item-10Sub Item – 93
Category-3Sub Category -5Item-10Sub Item – 94
Category-3Sub Category -5Item-10Sub Item – 95
Category-3Sub Category -5Item-10Sub Item – 96
Category-3Sub Category -5Item-10Sub Item – 97
Category-3Sub Category -5Item-10Sub Item – 98
Category-3Sub Category -5Item-10Sub Item – 99
Category-3Sub Category -5Item-10Sub Item – 100
Category-3Sub Category -5Item-11Sub Item – 101
Category-3Sub Category -5Item-11Sub Item – 102
Category-3Sub Category -5Item-11Sub Item – 103
Category-3Sub Category -5Item-11Sub Item – 104
Category-3Sub Category -5Item-11Sub Item – 105
Category-3Sub Category -5Item-11Sub Item – 106
Category-3Sub Category -5Item-11Sub Item – 107
Category-3Sub Category -5Item-11Sub Item – 108
Category-3Sub Category -5Item-11Sub Item – 109
Category-3Sub Category -5Item-11Sub Item – 110
Category-3Sub Category -5Item-12Sub Item – 111
Category-3Sub Category -5Item-12Sub Item – 112
Category-3Sub Category -5Item-12Sub Item – 113
Category-3Sub Category -5Item-12Sub Item – 114
Category-3Sub Category -5Item-12Sub Item – 115
Category-3Sub Category -5Item-12Sub Item – 116
Category-3Sub Category -5Item-12Sub Item – 117
Category-3Sub Category -5Item-12Sub Item – 118
Category-3Sub Category -5Item-12Sub Item – 119
Category-3Sub Category -5Item-12Sub Item – 120
Master Drop-Down Table

6. Once you done with creating Master Table data then time to move on to ‘Drop Down’ sheet.
Here, we are not going to do anything in ‘Drop Down’ worksheet. This sheet will be managed and used by VBA itself to create temporary drop-down lists for combo boxes, lists and other controls as data selection by user.

7. Now, we are done with worksheets structures, command button and master table. Let’s move to the VBA window to create a User Form, 4 level of combo boxes and utilize Custom VBA function to make it dynamic and dependent drop-down lists.
To move to the VBA window, you just need to click on Developer Tab and then click on Visual Basic button available in Code Group (very first button from left in Developer tab ribbon). Alternatively, you can also press ALT + F11 to jump to Visual Basic Application window.

Open VBA window

8. In VBA window, we need to add a UserForm. Click on Insert menu then select UserForm to add a new form in our project.

9. Design the form as shown in below image with properties:
User Form
Form Name : frmUser
Caption: Multi Level Dynamic Drop-Down
ShowModal: False
Height: 210
Width: 309

Combo boxes
Category : cmbCategory
Sub Category : cmbSubCategory
Item: cmbItem
Sub Item: cmbSubItem

Command Buttons

Submit Button : cmdSubmit
Cancel Button: cmdCancel

9. Now, we are done with designing the UserForm with 4 level of drop-down fields. Let’s add a blank module in our project to write the function. To add a blank module, click on Insert menu then select Module.

10. Change the module name to mdlDynamicDropDown as shown in below image.(optional step)

11. Go to the Module Code window and past the below code (predefined user defined function).

12. Now, we have created the function. Let’s understand it.
In this function, we have 5 different Parameters. See the explanation of parameters:
FilterColumn – This is a numeric value which indicates the column position in Master Sheet where we need to apply filter so that next level of Drop-down list can ben generated.
FilterText – It’s string value which need to be applied as filter value on FilterColumn. Usually, it should be the previous Comb Box or Drop-down field values. If you are using this function for first level of Drop-down field then you can pass NULL as a parameter.
DropDownColumn – This is numeric value which indicates the column position of Drop-down field available in Master Sheet. If you are using this function for first level of drop-down filed then FilterColumn and DropDownColumn would be always same (usually 1) with FilterText NULL.
MasterSheet – Provide the name of sheet where you have created master table for dropd-down.
DropDownSheet – Provide the name of sheet which you have created for drop-down and kept it as blank so that VBA code can utilize to create dynamic drop down fields.

Now, we are good with functions and parameters. Let’s start utilizing this function to create dynamic drop-down.
First of all, we need to create the drop-down for Category drop-down. To create the drop-down, just move to the code window of UserForm and then write the below code under UserForm_Initialize event.

For rest of the drop-downs, we need to call this function on change events of Combo-boxes.
So to create drop-down for Sub Category, we will call this function on Category change event. Please see the below code.

To create the drop-down for Item, we need to call this function on Change event of Sub Category. Please see the below code.

Now, we are left with Sub Item only. So we need to call this function on change event of Item. Please see the code.

So we are done with all the codes. We can write the code to open the UserForm on click event of ‘Show My Form’ button available on Home sheet.

Now, we are done with designing the form and calling this function to create multilevel drop-downs.

Please click on below button to download the demo file.

Download
Click to download

LEAVE A REPLY

Please enter your comment!
Please enter your name here