This technique shows how to create dynamic dropdown lists in VBA. Dynamic dropdowns in this context refers to a dynamically changing secondary list, that changes dependent upon a selection from a primary list. An example is a primary list of continents, and a secondary list showing the countries associated with the selected continent.

Below is an example of dynamic dropdowns, to demonstrate the technique, As this is a web page, this is a JavaScript solution, but the VBA solution works in a similar way.

Continent	Country	City
America Europe Africa Far East Indian Sub-Continent	[nothing selected]	[nothing selected]

This page demonstrates how such a technique might be implemented. In addition, at the end of the article, you can download an example workbook that provides all of the code and data discussed here. The workbook is a fully working example that can be used for your own projects, all that is needed is to change the tables/lists on the 'Data' worksheet to your own data.

In this discussion two separate implementations are provided, one using comboboxes as the dropdowns, and one using Data Validation lists as the dropdowns.The example workbook shows both implementations.

The article is segmented into the following sections:

Sample Data
Naming The Ranges
Combobox Version
Data Validation Version
Adding Further Dropsdowns
Download

Sample Data

The first step is to create a set of tables/lists similar to the table below. This is a truncated version of the tables in the example workbook.

Naming The Ranges

These tables are used in the code to poulate the dropdowns. The values in row 1, from column B on, are the groups that are used to populate the primary dropdown. This set of values is given a workbook name of 'List1Values'. The values in row 2 to row n for each of the populated columns are defined as workbook named ranges which are used by the VBA code to populate the secondary dropdown, based upon the value chosen in the primary dropdown. Each column of values is given a separate workbook named range, and because they are used in the VBA code, these secondary lists have to be named in a very structured manner. In this code, I am using named ranges of 'List2_1', 'List2_2', etc.

In practice, the VBA code should take care of creating the named ranges, simply by examining the data, and defining the named ranges accordingly. This allows further secondary lists to be added without any other user action, and/or extra items or changes in any of the secondary lists. The code that creates the named ranges is shown here:-

Public Const kApp As String = "Dynamic Dropdowns"

Public Const kList1Hnd As String = "List1Values"
Public Const kList2Hnd As String = "List2_"

'---------------------------------------------------------------------
Public Sub pzLoadList2Lists()
'---------------------------------------------------------------------
Dim oWsData As Worksheet
Dim cRows As Long, cCols As Long, i As Long, j As Long

    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    On Error GoTo load_exit

    Set oWsData = data
    With oWsData
        'create dynamic range names for List1 and List2 lists
        cCols = .Cells(1, Columns.Count).End(xlToLeft).Column
        For i = 2 To cCols
            cRows = .Cells(Rows.Count, i).End(xlUp).Row
            ThisWorkbook.Names.Add Name:=kList2Hnd & i - 1, _
                                   RefersToR1C1:="='" & data.Name & _
                                   "'!R2C" & i & ":R" & cRows & "C" & i
        Next i
    End With
    ThisWorkbook.Names.Add Name:=kList1Hnd, _
                           RefersToR1C1:="='" & data.Name & "'!R1C2:R1C" & cCols

load_exit:
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True

End Sub
			

This code should be called from the Workbook_Open event, in order to initially populate the comboboxes upon opening the workbook, and also whenever any change is made to the data (in that worksheet's Worksheet_Change event).

The other item that needs to be setup is the codename for the worksheets. This is necessary as the worksheet is referred to in the VBA code by its codename, in case the worksheet name is changed by a user. This code assumes that that 'Data' worksheet has a codename of 'data', that the combobox example worksheet has a codename of 'combo', and that the Data Validation example worksheet has a codename of 'dv'.

Combobox Version

So, how does it all work? As mentioned above, the named ranges are defined by VBA code called whenever the workbok is opened, and re-defined whenever data on that worksheet changes. Another worksheet is required that will hold the dropdowns that will have the dynamic functionality. In my example, I called this worksheet 'Combo Example', and added two control toolbox comboboxes to it, which are named as 'cboPrimary' and 'cboSecondary'.

The secondary combobox is loaded as part of the primary combobox change event, that is, selecting a value from the primary combobox triggers the following code:

'---------------------------------------------------------------------
Private Sub cboPrimary_Change()
'---------------------------------------------------------------------
Dim idx As Long
Dim iTargetCol As Long
Dim oFoundCell As Range

    With data.Range("List1Values")
        Set oFoundCell = .Find(what:=cboPrimary.Value, _
                               LookIn:=xlValues)
        If oFoundCell Is Nothing Then
            MsgBox "Critical error", vbCritical, "(xld) Dynamic DropDowns"
            Exit Sub
        End If
    End With

    'load the List2 dropdown and set the default to item 1
    iTargetCol = oFoundCell.Column - 1
    pzPopulateList2 iTargetCol

End Sub
			

In this code, the range 'List1Values' is searched for the value selected in the primary combobox. This search returns the ordinal number within those values, which in turn is passed as the secondary list range index to the pzPopulateList2 procedure to re-populate the secondary combobox.

The final piece of the jigsaw for this combobox solution is the code for populating the primary and secondary comboboxes. The following code shows how this is achieved.

'---------------------------------------------------------------------
Public Function pzPopulateList1()
'---------------------------------------------------------------------
Dim i As Long

    Application.EnableEvents = False
    On Error GoTo pl1_exit

    With combo.cboPrimary
        .Clear
        For i = 2 To Range(kList1Hnd).Count + 1
            .AddItem data.Cells(1, i).Value
        Next i

        Application.EnableEvents = True
        .ListIndex = 0
    End With

pl1_exit:
    Application.EnableEvents = True

End Function

'---------------------------------------------------------------------
Public Function pzPopulateList2(idx As Long)
'---------------------------------------------------------------------
Dim i As Long
Dim sList As String

    Application.EnableEvents = False
    On Error GoTo pl2_exit

    sList = "=" & kList2Hnd & CStr(idx)

    With combo.cboSecondary
        .Clear
        For i = 1 To Range(sList).Count
            .AddItem Range(sList).Cells(i, 1).Value
        Next i

        Application.EnableEvents = True
        .ListIndex = 0
    End With

pl2_exit:
    Application.EnableEvents = True
End Function
			

The procedure pzPopulateList1 takes each of the items in the named range 'List1Values', and loads them into the primary combobox.

The pzPopulateList2 procedure is just a little more complex, in that it is passed an index argument. This index refers to the ordinal number of the item selected in the primary combobox, and is used to determine which secondary list will be loaded into the secondary combobox. Using the named range structure mentioned earlier, this index is used to identify which named range is loaded into the secondary combobox.

Data Validation Version

The Data Validation version uses very similar techniques to the Combobox version , but the population code is specific to Data validation. In this implementation, two cells are used that utilise the Data Validation functionality, specifically Data Validation using custom lists. These two cells cover the primary data list, and the secondary, related, data list.

This implementation utilises the built-in Excel Data Validation functionality, and simply sets the function up with the correct lists. Again, the named ranges are used in the VBA code to facilitate the loading, for two reasons. Firstly, I find named ranges very flexible and powerful, and secondly and most importantly, Data Validation can only refer to ranges on another worksheet if named ranges are used. The same VBA code that automatically names the ranges in the combobox implementation is used for the Data Validation implementation.

As already mentioned, the Data Validation implementation works in a similar manner to the combobox implementation in that both Data Validation cells need to be initially populated when the workbook is opened. This is the code in the Workbook_Open event that performs that function

'---------------------------------------------------------------------
Private Sub Workbook_Open()
'---------------------------------------------------------------------
Dim cell As Range

    pzLoadList2Lists

    Application.DisplayAlerts = False

    'this poulates the Data Validation lists
    Set cell = dv.Range(kList1)
    fzCreateValidationList1 cell
    fzCreateValidationList2 cell.Offset(1, 0), 1, cell

    Application.DisplayAlerts = True

End Sub
			

The primary and secondary Data Validation cells also need to be re-populated if there is any change to the data tables/lists on the 'Data' worksheet. Again, this is achieved using Worksheet_Change event code, which is shown below

'---------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'---------------------------------------------------------------------
Dim oFoundCell As Range
Dim iTargetCol As Long

    If Not Intersect(Range(kList1), Target) Is Nothing Then
        If Target.Count = 1 Then

            With data.Range(kList1Hnd)
                Set oFoundCell = .Find(what:=Target.Value, _
                                       LookIn:=xlValues)
                If oFoundCell Is Nothing Then
                    MsgBox "Critical error"
                    Exit Sub
                End If
            End With

            'load the List2 dropdown and set the default to item 1
            iTargetCol = oFoundCell.Column - 1
            fzCreateValidationList2 Target.Offset(1, 0), iTargetCol, Target
            Target.Offset(1, 0).Value = data.Range(kList2Hnd & iTargetCol).Value
        End If

    End If

End Sub
			

And finally, here is the code that actually sets up the Data Validation in the two cells. The approach taken here is to delete any exising data validatioon already defined to that cell, and then re-create anew based upon the actual data in the tables/lists. The primary Data Validation cell is quite simple as it just assigns the named range 'List1Values' to the Data Validation set up in that cell. The secondary Data Validation is just a little more complex as it is passed the primary list index as an arguement which is used to construct the name of the range to be assigned, based upon the value 'List2_' and the index, and the error message reflects the value selected from the primary Data Validation cell.

'---------------------------------------------------------------------
Public Function fzCreateValidationList1(Target As Range)
'---------------------------------------------------------------------
    With Target.Validation
        .Delete
        .Add Type:=xlValidateList, _
             AlertStyle:=xlValidAlertWarning, _
             Formula1:="=" & kList1Hnd
        .InCellDropdown = True
        .InputTitle = kApp
        .ErrorTitle = kApp & " - Error"
        .InputMessage = ""
        .ErrorMessage = "This is not a valid Value"
    End With
End Function

'---------------------------------------------------------------------
Public Function fzCreateValidationList2(Target As Range, _
                                        idx As Long, _
                                        source As Range)
'---------------------------------------------------------------------
    With Target.Validation
        .Delete
        .Add Type:=xlValidateList, _
             AlertStyle:=xlValidAlertWarning, _
             Formula1:="=" & kList2Hnd & idx
        .InCellDropdown = True
        .InputTitle = kApp
        .ErrorTitle = kApp & " - Error"
        .InputMessage = ""
        .ErrorMessage = "This is not a valid Value for " & source.Value
    End With
End Function
			

There is just one other thing to note regarding Data Validation. The example given above works perfectly well in Excell 2000 and on, but if you have Excel 97, you will find that changing the value in the primary cell does not cause an update of the list associated with the secondary cell. This is because a change to a data validation cell does not fire the Worksheet_Change event in Excel 97. The solution to this is to use another event. I have used the Worksheet_Calculate event, and to trigger it I have referenced the primary Data Validation cell in an other cell (in which I have set the font colour to white, to hide it), with a simple =List1. Thus, when a change is made to the primary Data Validation cell, this changes the linked cell, which in turn triggers the Worksheet_Calculate event. The example workbook uses this technique, so it works with Excel97 on.

Ading Further Dropdowns

The code presented is fully-inclusive, and can handle any number of dropdowns (although the more you have, the slower it is bound to become, and the lists could become unmanageable). If you want to add further dropdowns, this is an outline of the process:

• Open the workbook with macros disabled
• Add a fourth control toolbox combobox and name it cboList4
• On the data sheet add a fourth column labelled List4
• Add data in column 4, inserting rows for repetition of Lists3, 2 and 1
• Close the workbook
• Open it with macros enabled

Try it with a few values at the top to start with, it is straight-forward.

Download

Click here to download an example workbook.