Multi-Select Listbox Functions

Listed here are some useful functions when using Multi-Select Listboxes on forms.  An Example Application is available to download to demonstrate how there functions are used. 

Functions:

• Select All items in Listbox
• Clear All selected items in Listbox
• Count all items in Listbox
• Count all selected items in Listbox
• Open a Report based on items selected
• Build SQL statement based on items selected

The Multi-Select property of the listbox must be set to Simple or Extended in order for the listbox control to accept multiple sections.

If the ColumnHead property is set to true then the first row (0) in the listbox will be the heading.

When a Multi-Select listbox is used, checking for nulls to determine 'if the user has selected something will not work. Instead check to see if the total count of selected items is equal to zero.

Use the ItemData Property to obtain the value of the Bound Column.  Use the Column Property to obtain the value of any other Column .

Sub Clear_MultiSelect()
'Clears all values Selected in a listbox

    Dim varItm As Variant
    Dim ctl As Control

    Set ctl = Me.lstMultiSelect
    
    For Each varItm In ctl.ItemsSelected
        ctl.Selected(varItm) = False
    Next varItm

End Sub

--------------------------------------------

Sub SelectAll_MultiSelect()
'Select All values in a listbox

    Dim lngCounter As Long
    Dim ctl As Control

    Set ctl = Me.lstMultiSelect
    
    For lngCounter = Abs(ctl.ColumnHeads) To ctl.ListCount
        ctl.Selected(lngCounter) = True
    Next lngCounter

End Sub

--------------------------------------------

Function Count_MultiSelect() As Long
'Counts All values in a listbox

    Dim ctl As Control

    Set ctl = Me.lstMultiSelect
    Count_MultiSelect = ctl.ListCount - Abs(ctl.ColumnHeads)
    
End Function

--------------------------------------------

Function CountSelected_MultiSelect() As Long
'Counts All Selected values in a listbox
    
    Dim varItm As Variant
    Dim lngCount As Long
    Dim ctl As Control

    Set ctl = Me.lstMultiSelect
    
    For Each varItm In ctl.ItemsSelected
        lngCount = lngCount + Abs(ctl.Selected(varItm))
    Next varItm
    
    CountSelected_MultiSelect = lngCount

End Function

--------------------------------------------

Function SQL_Criteria() As String
'Build Where Condition for SQL Statement (Bound Column - Numerica data type)
    
    Dim varItm As Variant
    Dim ctl As Control
    Dim strCriteria As String

    Set ctl = Me.lstMultiSelect
    
    For Each varItm In ctl.ItemsSelected
        'Use the ItemData Property to select the Bound Column
        'Use the Column Property to specify the Row, Column
        strCriteria = strCriteria + ctl.ItemData(varItm) & ","
    Next varItm
    If strCriteria = "" Then
        SQL_Criteria = " ID Like '*' "
    Else
        SQL_Criteria = " ID IN(" & Left(strCriteria, Len(strCriteria) - 1) & ")" 
		'Rremove last comma
    End If

End Function

--------------------------------------------

Function SQL_Criteria_Str() As String
'Build Where Condition for SQL Statement (Other Columns - String data type)
    
    Dim varItm As Variant
    Dim ctl As Control
    Dim strCriteria As String

    Set ctl = Me.lstMultiSelect
    strCriteria = "'"
    
    For Each varItm In ctl.ItemsSelected
        'Use the ItemData Property to select the Bound Column
        'Use the Column Property to specify the Row, Column
        strCriteria = strCriteria + ctl.Column(1, varItm) & "','"
    Next varItm
    
    If strCriteria = "'" Then
        SQL_Criteria_Str = " TestData Like '*' "
    Else
        SQL_Criteria_Str = " TestData IN(" & Left(strCriteria, Len(strCriteria) - 2) & ")" 
		'Rremove last comma & quote
    End If

End Function