In this post, which will evolve with time, I hope to slowly build up a series of useful tidbits of information and code samples relating to working with List boxes. This will hopefully become something similar to what I did when I created my article on the Web Browser control:

which, surprisingly enough to me, has turned out to be one of my most popular posts.

In the this article, the major highlights include:

• Select All
• Select None
• Select First
• Select Last
• Select a Specific Value

Select All

The first functions I want to present is that of Allen Browne from his article

Public Function lst_SelectAll(lst As Access.ListBox) As Boolean
'Purpose:   Select all items in the multi-select list box.
'Return:    True if successful
'Author:    Allen Browne. http://allenbrowne.com  June, 2006.
'Source:    http://allenbrowne.com/func-12.html
    On Error GoTo Error_Handler
    Dim lngRow                As Long

    If lst.MultiSelect Then
        For lngRow = 0 To lst.ListCount - 1
            lst.Selected(lngRow) = True
        Next
        lst_SelectAll = True
    End If

Error_Handler_Exit:
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: lst_SelectAll" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Select None

Once again, inspired by Allen Browne’s from his article

I wanted to present code to deselect all the items in a list box, so you can clear it. When I tested his code I found it didn’t work properly with non-multiselect list boxes, so here is my revised version. That said, I want to do some more testing as I find it highly suspicious that his code would fail!

'---------------------------------------------------------------------------------------
' Procedure : lst_SelectNone
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Deselect all the items in a List Box
'               Modified version of Allen Browne's code as the case for non-multiselect
'               did not work when tested.
' Return    : True if successful
' Req'd Refs: None required
'
' Original Author:    Allen Browne. http://allenbrowne.com  June, 2006.
' Original Source:    http://allenbrowne.com/func-12.html
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' lst       : List box to deselect all the items of
'
' Usage:
' ~~~~~~
' ? lst_SelectNone(Forms!Orders.Form.lst_Fruits)
'   Returns -> True
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2023-01-16              Initial Public Release
'---------------------------------------------------------------------------------------
Public Function lst_SelectNone(lst As Access.ListBox) As Boolean

    On Error GoTo Error_Handler
    Dim varItem               As Variant

    For Each varItem In lst.ItemsSelected
        lst.Selected(varItem) = False
    Next
    lst_SelectNone = True

Error_Handler_Exit:
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: lst_SelectNone" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Select First

Below is the code to select the 1st item in a list box.

'---------------------------------------------------------------------------------------
' Procedure : lst_SelectFirst
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Select the first item in a list box
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' lst       : List box to select the first item of
'
' Usage:
' ~~~~~~
' ? lst_SelectFirst(Forms!Orders.Form.lst_Fruits)
'   Returns -> True
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2023-01-16              Initial Public Release
'---------------------------------------------------------------------------------------
Public Function lst_SelectFirst(lst As Access.ListBox) As Boolean
On Error GoTo Error_Handler

    If lst.ListCount > 0 Then lst.Selected(0) = True
    lst_SelectFirst = True
    
Error_Handler_Exit:
    On Error Resume Next
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: lst_SelectFirst" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Select Last

The following code will select the last item in a list box.

'---------------------------------------------------------------------------------------
' Procedure : lst_SelectLast
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Select the last item in a list box
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' lst       : List box to select the last item of
'
' Usage:
' ~~~~~~
' ? lst_SelectLast(Forms!Orders.Form.lst_Fruits)
'   Returns -> True
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2023-01-16              Initial Public Release
'---------------------------------------------------------------------------------------
Public Function lst_SelectLast(lst As Access.ListBox) As Boolean
On Error GoTo Error_Handler

    If lst.ListCount > 0 Then lst.Selected(lst.ListCount - 1) = True
    lst_SelectLast = True
    
Error_Handler_Exit:
    On Error Resume Next
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: lst_SelectLast" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Select Value

This function can be used to select a specific item in a listbox

'---------------------------------------------------------------------------------------
' Procedure : lst_SelectValue
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Select a specific item in a list box
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' lst       : List box to select the item of
' sValue    : Value to find and select
' lCol      : Optional - list box column to search in. (Zero based, 0 being the default)
'
' Usage:
' ~~~~~~
' ? lst_SelectValue(Forms!Orders.Form.lst_Fruits, "Grapes") 'Search 1st column
'   Returns -> True
' ? lst_SelectValue(Forms!Orders.Form.lst_Fruits, "Grapes", 0) 'Search 1st column
'   Returns -> True
' ? lst_SelectValue(Forms!Orders.Form.lst_Fruits, "Grapes", 1) 'Search 2nd column
'   Returns -> True
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2023-01-16              Initial Public Release
'---------------------------------------------------------------------------------------
Public Function lst_SelectValue(lst As Access.ListBox, _
                                sValue As String, _
                                Optional lCol As Long = 0) As Boolean
    On Error GoTo Error_Handler
    Dim lCounter              As Long

    For lCounter = 0 To lst.ListCount - 1
        'Simple Case
        '        If lst.ItemData(lCounter) = sValue Then
        '            lst.Selected(lCounter) = True
        '            lst_SelectValue = True
        '        End If
        'More complex cases - multi-column list boxes
        If lst.Column(lCol, lCounter) = sValue Then
            lst.Selected(lCounter) = True
            lst_SelectValue = True
        End If
    Next

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: lst_SelectLast" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Other Resources on the Subject

ListBox.ListCount property (Access)

Use the ListCount property to determine the number of rows in a list box. Read/write Long.

ListBox.ItemsSelected property (Access)

Use the ItemsSelected property to return a read-only reference to the hidden ItemsSelected collection. This hidden collection can be used to access data in the selected rows of a multiselect list box control.

ListBox.ItemData property (Access)

The ItemData property returns the data in the bound column for the specified row in a list box. Read-only Variant.

ListBox.Column property (Access)

Use the Column property to refer to a specific column or column and row combination in a multiple-column combo box or list box. Read-only Variant.