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).

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.

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).

 
  'Parameters - FilterColumn: Where we need to apply filter, FilterText : filter text value, DropDownColumn:Which column is for drop-down
 
  Function DynamicList(FilterColumn As Integer, FilterText As Variant, DropDownColumn As Integer, MasterSheet As String, DropDownSheet As String) As String
       
    'If FilterText is blank then exit from function
    If FilterText = "" Then Exit Function
    
    'On Error GoTo err_handler
        
    Dim shMaster As Worksheet
    Dim shDropDown As Worksheet
    
    Dim iMasterLastRow As Double
    Dim iMasterLastColumn As Double
    Dim iDropDownLastRow As Integer
    
       
    Set shMaster = ThisWorkbook.Sheets(MasterSheet)
    Set shDropDown = ThisWorkbook.Sheets(DropDownSheet)
    
    'removing existing filters, if any
    
    If shMaster.AutoFilterMode Then shMaster.AutoFilterMode = False
    
    
   'Applying filter in Master Data Sheet
   
    With shMaster
     
     iMasterLastRow = .Range("A" & .Rows.Count).End(xlUp).Row 'identify the last row number in Master Sheet
     
     iMasterLastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column 'identify the last column number in Master Sheet
    
    'Applying Filter in Master Sheet
    .Range(.Cells(1, 1), .Cells(iMasterLastRow, iMasterLastColumn)).AutoFilter Field:=FilterColumn, Criteria1:=FilterText
   
    End With
   
                   
   'Removing the existing data from the drop-down column number
   
    With shDropDown
     
    .Range(.Cells(1, DropDownColumn), .Cells(Rows.Count, iMasterLastColumn)).ClearContents
      
    End With
    
    'Copying the filter drop down column basis on filter value and paste in Drop Down sheet
       
    With shMaster
      
     .Range(.Cells(1, DropDownColumn), .Cells(iMasterLastRow, DropDownColumn)).SpecialCells(xlCellTypeVisible).Copy shDropDown.Cells(1, DropDownColumn)
    
     .AutoFilterMode = False 'Removing filters
          
    End With
      
   
    With shDropDown
    
    'Removing duplicates from all the columns
     
    .Columns(DropDownColumn).RemoveDuplicates Columns:=1, Header:=xlYes
    
    'identifying last row number in drop-down sheet
     iDropDownLastRow = .Cells(Rows.Count, DropDownColumn).End(xlUp).Row
     
     'code to exclude column headers if no value in dropdown column in drop down sheet
     iDropDownLastRow = IIf(iDropDownLastRow < 2, 2, iDropDownLastRow) 'code to exclude header if only header there
        
     'Assigning the range in function name to return the value
     DynamicList = "'Drop Down'!" & .Range(.Cells(2, DropDownColumn), .Cells(iDropDownLastRow, DropDownColumn)).Address
    
    
    End With
   
    Exit Function
   
err_handler:
    
    MsgBox Err.Description
       
End 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.

Private Sub UserForm_Initialize()

    cmbCategory.RowSource = DynamicList(1, Null, 1, "Master Sheet", "Drop Down")
    
End Sub

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.

Private Sub cmbCategory_Change()

    
    cmbSubCategory.RowSource = DynamicList(1, cmbCategory.Value, 2, "Master Sheet", "Drop Down")
    
End Sub

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

Private Sub cmbSubCategory_Change()

   
    cmbItem.RowSource = DynamicList(2, cmbSubCategory.Value, 3, "Master Sheet", "Drop Down")
    
End Sub

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

Private Sub cmbItem_Change()

    
    cmbSubItem.RowSource = DynamicList(3, cmbItem.Value, 4, "Master Sheet", "Drop Down")
    
End Sub

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.

Private Sub CommandButton1_Click()
    frmUser.Show
End Sub

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.