Linked List Boxes

This page describes code that you can use to limk several list boxes together.

Introduction

ListBox controls are a very common and useful way to allow the user to choose a selection from a set of possible values. However, the ListBox control does not have any built-in mechanism for creating a set of linked ListBox controls. By "linked", we mean that changing the selected value in one list box causes the list of possible elements in another list box to change. However, using the class module described on this page, along with a few lines of code in your code module, you can have as many linked ListBox controls as you want. A set of linked ListBox controls typically are used to represent some hierarchy of data choices. In the examples on this page, we will use a simplified hierarchy of disc drives, folders, and files. Here, the list of drives is the initial or primary ListBox, and changing its selected value will change the value list in the subordinate or downstream ListBoxes, the Folder and File ListBoxes. Thus, the Folder ListBox is child of the Drive ListBox, and the File ListBox is a child of the Folder ListBox.

The code described on this page works only for ActiveX ListBoxes that are inserted on to a worksheet via the Controls command bar (or ActiveX panel in Excel 2007 and later) and for ListBoxes that are used on UserForms. The code does not support Excel's native ListBox, on the Forms menu.

In this example, each Drive has a list of Folders, and each Folder has a list of Files. If the selected value in the Drive ListBox is changed, the list of values in the Folder ListBox changes, and this in turn causes the list of Files to change. When the selected value of one ListBox is changed, the lists of values in all child and downstream ListBoxes are changed.

The driving mechanism that links the controls together is a class named CLinkedListBox, which is available in the downloadable example workbook. This class has two properties that are used to handle the linking. The first property, LBX is the ListBox control that is associated with the instance of the class. Each instance of the CLinkedListBox has one and only one ListBox control associated with it. The second property is NextListBox, which is itself an instance of CLinkedListBox, and references the ListBox that is one level lower in the hierarchy. So, in our example, the NextListBox of the Drive CLinkedListBox object is the Folder CLinkedListBox, and, in turn, the NextListBox of the Folder CLinkedListBox is the File CLinkedListBox. Since the File object is the end of the hierarchy, it's NextListBox is Nothing. This example uses only three layers in the hierarchy (Drive -> Folder -> File), but in practice there is no limit to the number of linked ListBox controls.

Defining The Data

The data that is used to populate the ListBox controls is stored in named ranges on a worskheet. There needs to be one range defined for every element in every list boxes. Each defined name has as many rows as there are items in the ListBox's List and three columns. The first column in the text to display in the ListBox. This can be any text, including spaces and special characters. The second column is the name of a Defined Name that contains the information for the next ListBox. The third column can be used include (if TRUE) or exclude (if FALSE) individual rows in a ListBox. Continuing our previous example, the range for the Drives ListBox would be named RDrives (or any name you want) and that range would have one row for each drive, similar to the following:

Here, C: and D: are the text values that are to be displayed in the Drive ListBox. This can be any text you want, allowing spaces and special characters. The second element of each row, DriveRangeC and DriveRangeD, are the defined names from which the ListBox specified in NextListBox should load its values. For example, the defined name DriveRangeC specifies the list used by the Folder ListBox when C: is selected in the Drive ListBox. If D: is selected, the Folder ListBox is populated from the range DriveRangeD. The third column of the defined name is to be either TRUE or FALSE. If TRUE (or non-zero), that item will appear in the ListBox. If FALSE, that item is not included in the ListBox.

The ranges that define the content and behavior of the ListBox controls may be located anywhere on any worksheet and may be on different worksheets There is no requirement that one ListBox range be in any spatial relationship with any other ListBox range. The lists may have any number of rows and each range may contain differing numbers of rows.

For the final ListBox at the end of the hierarchy (the File ListBox in our example), does not have a downstream linked ListBox, so the second column of the range is empty.

Setting Up The ListBoxes

The ListBox controls used as linked ListBoxes should be defined with 2 columns, with the second column hidden. Change the ColumnCount property to 2 and set the ColumnWidths property to nnn;0 where nnn is approximately 90% of the ListBox's total width. The ListBoxes may be placed anywhere, including across multiple worksheets. There is no requirement that there be any spatial relationship among the ListBoxes.

In your VBA Project, import the CLinkedListBox class module. Then, in a regular code module, declare a project-scoped variable for each ListBox to be linked:

   Public LinkedLbxDrives As CLinkedListBox
   Public LinkedLbxFolders As CLinkedListBox
   Public LinkedLbxFiles As CLinkedListBox

Then, associate each of these variables with an existing ListBox and set the NextListBox property:

Sub InitializeListBoxes()
    ' instantiate the CLinkedListBox objects:
    Set LinkedLbxDrives = New CLinkedListBox
    Set LinkedLbxFolders = New CLinkedListBox
    Set LinkedLbxFiles = New CLinkedListBox
    
    ' assign a list box to each object:
    Set LinkedLbxDrives.LBX = Sheet1.lbxDrives
    Set LinkedLbxFolders.LBX = Sheet1.lbxFolders
    Set LinkedLbxFiles.LBX = Sheet1.lbxFiles
    
    ' assign NextListBox to set up the hierarchy
    Set LinkedLbxDrives.NextListBox = LinkedLbxFolders
    Set LinkedLbxFolders.NextListBox = LinkedLbxFiles
    ' the final listbox doesn't link to any listbox,
    ' so set NextListBox to Nothing.
    Set LinkedLbxFiles.NextListBox = Nothing
    
    ' initialize all of the linked list boxes
    ' by loading the first (top-level) list box.
    LinkedLbxDrives.LoadListBox Range("RDrives")
End Sub

This is all there is to it.

You can download the file with all the example code on this page.

CLinkedListBox Code Listing

The following is the complete code listing of the CLinkedListBox class.

Option Explicit
Option Compare Text
Option Base 1
'==============================================================================
'==============================================================================
' CLinkedListBox
' By Chip Pearson, chip@cpearson.com www.cpearson.com
'                  www.cpearson.com/Excel/LinkedListBoxes.aspx
' 18-June-2010
'
' This provides the logic for linking multiple list boxes to
' one another based on the selected item in a list box. That is to
' say, changing the selected item in ListBox1 can cause its "child"
' or "downstream" listboxes to update their list values.
' Each listbox in the chain of linked listboxes (with the exception
' of the final listbox) contains a reference to the next or "downstream"
' list box. When a listbox's value is changed, the LoadListBox member
' of the listbox's downstream list box is called, causing it to update
' its list and the lists of all the downstream listboxes.
'==============================================================================
'==============================================================================


'=============================================
' PRIVATE VARIABLES
'=============================================
' pLBX is the ListBox control that is
' managed by this instance of the class.
Private WithEvents pLBX As MSForms.ListBox
' pNextListBox in the next listbox in the
' natural order of the data hierarchy.
Private pNextListBox As CLinkedListBox
' pInitialIndex sets the listbox's
' ListIndex to any valid value.
Private pInitialIndex As Long

'=============================================
' PROPERTY PROCEDURES
'=============================================
Public Property Get LBX() As MSForms.ListBox
''''''''''''''''''''''''''''''''''''''''''''''
' Get LBX
' Returns the ListBox control managed by
' this instance of the class.
''''''''''''''''''''''''''''''''''''''''''''''
    Set LBX = pLBX
End Property

Public Property Set LBX(C As MSForms.ListBox)
''''''''''''''''''''''''''''''''''''''''''''''
' Set LBX
' This sets the ListBox control to be managed
' by this instance of the class.
'''''''''''''''''''''''''''''''''''''''''''''
    Set pLBX = C
End Property

Public Property Get NextListBox() As CLinkedListBox
''''''''''''''''''''''''''''''''''''''''''''''
' Get NextListBox
' This returns the next listbox in the chain
' of linked listboxes.
''''''''''''''''''''''''''''''''''''''''''''''
    On Error Resume Next
    Set NextListBox = pNextListBox
End Property

Public Property Set NextListBox(CLbx As CLinkedListBox)
''''''''''''''''''''''''''''''''''''''''''''''
' Set NextListBox
' This sets the NextListBox to the class that
' controls the next downstream listbox.
''''''''''''''''''''''''''''''''''''''''''''''
    Set pNextListBox = CLbx
End Property
    
Public Property Get InitialIndex() As Long
''''''''''''''''''''''''''''''''''''''''''''''
' Get InitialIndex
' This returns the InitialIndex value. This
' is the value to which the listbox's
' ListIndex is to be set.
''''''''''''''''''''''''''''''''''''''''''''''
    InitialIndex = pInitialIndex
End Property

Public Property Let InitialIndex(Value As Long)
''''''''''''''''''''''''''''''''''''''''''''''
' Let InitialIndex
' Sets the value of this class's InitialIndex
' property.
''''''''''''''''''''''''''''''''''''''''''''''
    If Value < 0 Then
        Err.Raise 5, , "InitialIndex must be greater than or equal to 0."
        Exit Property
    End If
    pInitialIndex = Value
End Property

'=============================================
' METHODS
'=============================================

Public Sub LoadListBox(DataRange As Range)
''''''''''''''''''''''''''''''''''''''''''''''
' LoadListBox
' This loads the listbox. It takes its
' values from the first column of DataRange
' and the ranges linked to each of those
' from the second item in the row.
''''''''''''''''''''''''''''''''''''''''''''''
    Dim R As Range
    Dim Arr() As String
    Dim N As Long
    N = DataRange.Rows.Count
    ReDim Arr(1 To N, 1 To 2)
    ClearList
    With Me.LBX
        N = 0
        For Each R In DataRange.Columns(1).Cells
            ' skip empty elements
            If (Len(Trim(R(1, 1).Text)) > 0) Then
                If R(1, 3).Value <> False Then
                    N = N + 1
                    Arr(N, 1) = R.Text
                    Arr(N, 2) = R(1, 2).Text
                End If
            End If
        Next R
        .List = Arr
        If .ListCount > 0 Then
            If (pInitialIndex <= .ListCount - 1) And _
                (pInitialIndex >= 0) Then
                .ListIndex = pInitialIndex
            Else
                .ListIndex = 0
            End If
            If Not pNextListBox Is Nothing Then
                ' call the next listbox's LoadListBox. This
                ' will cause a cascade down the listbox change.
                pNextListBox.LoadListBox Range(.List(.ListIndex, 1))
            End If
        End If
    End With
End Sub

Public Sub ClearList()
''''''''''''''''''''''''''''''''''''''''''''''
' ClearList
' Clear the list box and send a message down stream
' to clear all subordinate list boxes. Upstream
' listboxes are not cleared.
''''''''''''''''''''''''''''''''''''''''''''''
    Me.LBX.Clear
    
    If Not pNextListBox Is Nothing Then
        pNextListBox.ClearList
    End If
End Sub

Public Sub SetIndex(Value As Long)
''''''''''''''''''''''''''''''''''''''''''''''
' SetIndex
' This sets the current ListIndex for the
' LBX listbox controlled by this instance
' of the class. It calls the LoadListBox
' of the downstream listbox so all downstream
' listboxes will be properly updated. However,
' upstream listboxes are not updated, so it
' is possible that the (upstream) listboxes
' will be out of sync. Use this method with
' caution.
''''''''''''''''''''''''''''''''''''''''''''''
Dim S As String
If (Value < 0) Or (Value > Me.LBX.ListCount - 1) Then
    Err.Raise 5, , "Invalid Value for ListIndex"
    Exit Sub
End If
With Me.LBX
    .ListIndex = Value
    S = .List(.ListIndex, 1)
    pNextListBox.LoadListBox Range(S)
End With
End Sub

Private Sub pLBX_Click()
''''''''''''''''''''''''''''''''''''''''''''''
' pLBX_Click
' When the user clicks on a list box item,
' the range name from the hidden second column
' of the list box is passed to pNextListBox
' so that it (and other downstream listboxes
' can load with the right data.
'''''''''''''''''''''''''''''''''''''''''''
    Dim S As String
    With Me.LBX
        If .ListIndex < 0 Then
            Exit Sub
        End If
        S = .List(.ListIndex, 1)
        If Not pNextListBox Is Nothing Then
            pNextListBox.LoadListBox Range(S)
        End If
    End With
End Sub

This page last updated: 21-June-2010.