Forms: Callback function for a Multi Column List box

    Access List Boxes have always supported a callback function that can be used as a setting for the RowSourceType property.  A callback function not only gives you more control over how to fill the list box (Access will call the function for each row/column being filled), but it also allows you to circumvent maximum limit of a Value List.  For more information on a callback function, see the help topic on Miscellaneous Constants.

    Filling a multi column list box is only slightly different from the single column code sample provided in the help file.  We still need to use the same format of the callback function, and  handle a few things differently:

1. acLBGetColumnCount: Return the actual number of columns you want instead of 1.
2. acLBGetRowCount:  If you want the first row to carry column description, the value returned should be one more than the actual number of rows.
3. acLBGetColumnWidth: Return the width (in Twips) of each column.

    In each case, you'll need to also look at the column and row that Access is calling the function for.  Access passes these values as the varRow and varCol arguments of the function. In the case of the sample function for a three column listbox, you can see that for acLBGetColumnWidth and acLBGetValue, we need to use both the varRow and varCol arguments to figure out what to return back.

' ************* Code Start **************
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Function fListFill(ctl As Control, varID As Variant, varRow As Variant, _
                varCol As Variant, varCode As Variant) As Variant
'Callback function to fill the multirow-multicolumn listbox
'
Dim varRet As Variant
Const TWIPS = 1440
Const COLUMN_COUNT = 3
 
    On Error GoTo ErrHandler
    Select Case varCode
 
        Case acLBInitialize
            varRet = True
 
        Case acLBOpen
            varRet = Timer
 
        Case acLBGetRowCount
            varRet = UBound(atResults)
 
        Case acLBGetColumnWidth
            'Set the widths of the column
            'TWIPS converts to the appropriate
            'VBA Units of measurements, TWIPS.
            Select Case varCol
                Case 0:
                    varRet = 2.8 * TWIPS
                Case 1:
                    varRet = 0.5 * TWIPS
                Case 2:
                    varRet = 0.5 * TWIPS
            End Select
 
        Case acLBGetColumnCount
            varRet = COLUMN_COUNT
 
        Case acLBGetValue
            'Return the particular class member's value
            'depending on which column is being populated
            Select Case varCol
                Case 0:
                    ' First row contains column headings
                    If varRow = 0 Then
                        varRet = "Path"
                    Else
                        varRet = atResults(varRow).strFullPath
                    End If
                Case 1:
                    If varRow = 0 Then
                        varRet = "Size"
                    Else
                        varRet = atResults(varRow).lngSize
                    End If
                Case 2:
                    If varRow = 0 Then
                        varRet = "Type"
                    Else
                        varRet = atResults(varRow).strTypeName
                    End If
            End Select
    End Select
 
    fListFill = varRet
ExitHere:
    Exit Function
ErrHandler:
    Resume ExitHere
End Function
' ************* Code End **************