SelectedListBoxItems() Function

UPDATE [2022-07-15]: Please see my follow up article, IMPROVED: SelectedListBoxItems() Function, for a newer version of this function that incorporates Laurent Meus's comment below.

So you've got a multi-select list box that you want to use to filter a report, but how do you get the list of values that the user selected?

Brief Example

Here's a sample table of some soccer leagues from around the world:

Let's say you wanted to let your users choose one or more of the above leagues to filter a report to show only the teams from the selected league(s).  The easiest way to do that is with a multi-select list box control.  It's perfect for lists with fewer than ten items or so.

The list box above has two columns:

1. LeagueID (hidden)
2. LeagueName

Its "Multi Select" property is set to Extended, though Simple would work equally well.

Getting the selected values from such a list box control isn't hard, but I had to look up the properties I needed to use every time the situation came up.  I finally got tired of doing that, so I wrote a quick function to make the task simpler (and create more readable code).

Immediate Window Usage

The function returns a comma-delimited list of selected items from a list box.  

By default, it will return the values from the first column with no delimiter.  In a typical list box where the AutonumberID is the first (hidden) column, this is what we usually want.

Sometimes, though, we want to return the text values.  That's where the two optional parameters come in:

• Wrapper: which is generally a blank string ("") for numeric fields, "#" for date fields, and an escaped double quote ("""") for string fields
• ColumnNum: remember that column numbers in list boxes are 0-based, so the second column is ColumnNum:= 1.

Real World Usage

If the user can filter the values that appear in the reports, I like to show a human-readable note in the header to indicate which values are included in the report.  I often use the OpenArgs argument of the OpenReport method to pass that sort of information.

Here's a full sample of the kind of code I might write in the click event of a soccer team report preview button that relies on the list box shown above:

Private Sub btnShowReport_Click()
    Dim InList As String
    InList = SelectedListBoxItems(Me.lbLeaguePicker)
    
    Dim WhereClause As String
    WhereClause = "LeagueID In (" & InList & ")"
    
    Dim Leagues As String
    Leagues = SelectedListBoxItems(Me.lbLeaguePicker, """", 1)
    
    'see: https://nolongerset.com/previewreport-function/
    PreviewReport "SoccerTeams", WhereClause, Leagues
End Sub

The Code

As a periodic reminder, you generally won't see error-handling boilerplate in my code because I use Wayne Phillips's excellent vbWatchdog global error handler.

'---------------------------------------------------------------------------------------
' Procedure : SelectedListBoxItems
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/selectedlistboxitems/
' Date      : 12/14/2012
' Purpose   : Returns a comma delimited list of selected items from a list box.
' Notes     - Wrapper generally "" for numeric, "#" for dates, and """" for strings.
'---------------------------------------------------------------------------------------
'
Function SelectedListBoxItems(LBox As ListBox, _
                              Optional Wrapper As String = "", _
                              Optional ColumnNum As Integer = 0)
    Dim Row As Integer, s As String, CurItem As String
    For Row = 0 To LBox.ListCount - 1
        If LBox.Selected(Row) Then
            CurItem = Wrapper & LBox.Column(ColumnNum, Row) & Wrapper
            s = Conc(s, CurItem)
        End If
    Next Row
    SelectedListBoxItems = s
End Function

'The Conc() function concatenates two strings without needing to strip
'   off the leading/trailing delimiter
'Source: https://nolongerset.com/come-together/
Private Function Conc(StartText As Variant, NextVal As Variant, _
                      Optional Delimiter As String = ", ") As String
    If Len(Nz(StartText)) = 0 Then
        Conc = Nz(NextVal)
    ElseIf Len(Nz(NextVal)) = 0 Then
        Conc = StartText
    Else
        Conc = StartText & Delimiter & NextVal
    End If
End Function

Referenced articles