good example of listbox with sorting by clicking on column heading

[Mark Andrews]

Anyone have a good example of a listbox with sorting by clicking on column
heading?

Thanks,
Mark

 

[John Spencer]

Here is some generic code that I use to do this.

It has some limits. You can't have a horizontal scroll bar in the
listbox - so the total width of the columns that show must be less than
the total width of the listbox.

The listbox must be based on a query not a table.

It works to sort using the right-click event on the listbox. So you
don't need a column heading. If you want to limit it to sort only when
the column heading is clicked, you will have to modify the code.


Public Sub sSortListBox(anyListbox As Control, _
Button As Integer, _
Shift As Integer, _
X As Single)
'Purpose: Sort list box by column when column is right-clicked
'Author: Copyright by John Spencer
'Version Date: 04-14-2004
'Limitations:
' No Horizontal scroll bar in listbox
' RowSource must be query
' Uses DAO code; not tested with ADP
'Permission to use in applications is granted to all
'with the understanding that
'credit is given to the author.
'No warrantee or guaranty is given - use at your own risk.
'
'Code to sort list in ascending/descending order
'depending on which column is right-clicked
'and whether shift key is pressed.
'Uses the SQL syntax of specifying a column number as
'the sort column -
' SELECT ... FROM ... ORDER BY N
'- where N is integer reflecting the position of a field in SELECT clause.
'Install call to this code in the Mouse Down event of a listbox.
'Example -
' sSortListBox Me.SomeListbox, Button, Shift, X
'---------------------------------------------------------------------
'---------------------------------------------------------------------
'In the listbox's Mouse Up event add code to cancel the Mouse up event.
' If Button = acRightButton Then DoCmd.CancelEvent
'That line will stop any popup menu from appearing.
'---------------------------------------------------------------------
'---------------------------------------------------------------------

Dim strSQL As String
Dim vGetWidths As Variant
Dim vArWidths() As Variant
Dim iColCount As Integer, iColNumber As Integer
Dim iLoop As Integer
Dim iColWidthSum As Integer
Dim iUndefined As Integer
Dim iDefaultWidth As Integer
Dim strOrderBy As String
Const strListSeparator As String = ";" 'list Separator
'If your list separator is not a ";"
'you will need to change the ";" to your list separator

On Error GoTo ERROR_sSortListBox

If Button <> acRightButton Then
'only sort based on right button being clicked

ElseIf anyListbox.RowSourceType <> "table/query" Then
'only sort listbox based on queries
MsgBox "List box must use a query as it's row source"

ElseIf Len(anyListbox.RowSource) = 0 Then
'Nothing there, so ignore the click

ElseIf Not (InStr(1, Trim(anyListbox.RowSource), "Select",
vbTextCompare) = 1 _
Or InStr(1, Trim(anyListbox.RowSource), "Parameters",
vbTextCompare) = 1) Then
'If rowsource does not start with SELECT or PARAMETERS then
'assume it is a table not a query
MsgBox "List box must use a query as its row source"

ElseIf anyListbox.ColumnCount <> _
DBEngine(0)(0).CreateQueryDef("",
anyListbox.RowSource).Fields.Count Then
'Column count must be correctly set, otherwise this routine
'could cause errors. Column count set less than actual field count
'will cause subscript errors. Column count set higher than actual
'field count can cause listbox to display nothing if "Extra" column
'is clicked.
MsgBox "List box column count does not match query field count!"

Else 'passed the error checks

With anyListbox
'Column count must be correctly set, otherwise this routine
'could cause errors. Column count set less than actual column
count
'will cause subscript errors. Column count set higher than actual
'column count can cause listbox to display nothing if "Extra"
column
'is clicked.
iColCount = .ColumnCount
ReDim vArWidths(iColCount - 1, 0 To 1)

'If you are using Access 97 then you will need a custom
function to
'perform the parsing of the column widths into an array.
vGetWidths = Split(.ColumnWidths, strListSeparator, -1,
vbTextCompare)

'Assign values to array that holds length and running sum of
length
For iLoop = 0 To UBound(vGetWidths)
iColWidthSum = iColWidthSum + Val(vGetWidths(iLoop))
vArWidths(iLoop, 1) = iColWidthSum
vArWidths(iLoop, 0) = vGetWidths(iLoop)
Next iLoop

'Adjust any colwidths that are unspecified:
'The minimum is the larger of 1440
'or the remaining available width of the list box
'divided by number of columns
'with unspecified lengths.
For iLoop = 0 To iColCount - 1
If Len(vArWidths(iLoop, 0) & vbNullString) = 0 Then
iUndefined = iUndefined + 1
End If
Next iLoop

If iUndefined <> 0 Then
iDefaultWidth = (.Width - iColWidthSum) / iUndefined
End If

If iDefaultWidth > 0 And iDefaultWidth < 1440 Then
MsgBox "Sorry! Can't process listboxes with horizontal
scrollbars"
Exit Sub 'Horizontal scroll bar present
Else
'recalculate widths and running sum of column widths
iColWidthSum = 0
For iLoop = 0 To iColCount - 1
If Len(vArWidths(iLoop, 0) & vbNullString) = 0 Then
vArWidths(iLoop, 0) = iDefaultWidth
End If
iColWidthSum = iColWidthSum + Val(vArWidths(iLoop, 0))
vArWidths(iLoop, 1) = iColWidthSum

Next iLoop
End If

'Set right edge of last column equal to width of listbox
vArWidths(iColCount - 1, 1) = .Width

'Determine which column was clicked
For iLoop = 0 To iColCount - 1
'If X - .Left <= vArWidths(iLoop, 1) Then
If X <= vArWidths(iLoop, 1) Then
iColNumber = iLoop
Exit For
End If
Next iLoop
iColNumber = iColNumber + 1 'adjust since iLoop is 0 to n-1

'rebuild sql statement
If iColNumber > 0 And iColNumber <= iColCount Then
strSQL = Trim(.RowSource)

If Right(strSQL, 1) = ";" Then strSQL = Left(strSQL,
Len(strSQL) - 1)

iLoop = InStr(1, strSQL, "Order by", vbTextCompare)
If iLoop > 0 Then
strOrderBy = Trim(Mid(strSQL, iLoop + Len("Order by")))
strSQL = Trim(Left(strSQL, iLoop - 1))
End If

'Build the appropriate ORDER BY clause
If Shift = acShiftMask Then
'If shift key is down force sort to desc on selected column
strOrderBy = " Order By " & iColNumber & " Desc"

ElseIf Len(strOrderBy) = 0 Then
'If no prior sort then sort this column ascending
strOrderBy = " Order by " & iColNumber & " Asc"

ElseIf InStr(1, strOrderBy, iColNumber & " Asc",
vbTextCompare) > 0 Then
'If already sorted asc on this column then sort descending
strOrderBy = " Order By " & iColNumber & " Desc"

ElseIf InStr(1, strOrderBy, iColNumber & " Desc",
vbTextCompare) > 0 Then
'If already sorted desc on this column then sort Ascending
strOrderBy = " Order By " & iColNumber & " Asc"

Else
strOrderBy = " Order by " & iColNumber & " Asc"
End If

strSQL = strSQL & strOrderBy
.RowSource = strSQL

End If 'Rebuild SQL if col number is in range 1 to number of
columns
End With 'current list
End If 'Passed error checks

EXIT_sSortListBox:
Exit Sub

ERROR_sSortListBox:
Select Case Err.Number
Case 9 'Subscript out of range
MsgBox Err.Number & ": " & Err.Description & _
vbCrLf & vbCrLf & "Check column count property of list
box.", _
vbInformation, "ERROR: sSortListBox"

Case Else 'unexpected error
MsgBox Err.Number & ": " & Err.Description, vbInformation, _
"ERROR: sSortListBox"
End Select

Resume EXIT_sSortListBox
End Sub

 

[Mark Andrews]

John,

Very nice code!

Thank you very much for your help! It works perfectly for what I need.

Well not perfectly, I wish the built-in listbox control was better in Access
but I can live with right-clicking to sort.

Thanks again,
Mark