Toggle sort multicolumn listbox with headers as command buttons

[Yodelayheewho]

My userform has a multicolumn listbox with 10 columns. I created command buttons as my headers.
I searched for a way to sort each column by clicking its respective command button header. I also wanted the command button to 'toggle' so each consecutive click would toggle between ascending and descending order. I found the following thread that got me pretty far: How do I sort My Listbox in a Userform?

I entered this code for the first column. I works, the column sorts a to z, then z to a, etc. However, the rest of the column data doesn't sort with it. I'm very new to VBA, so my code has notes. Here is the code from the old thread with my controls and notes...

[/COLOR]
Option Explicit
[COLOR=rgb(97, 189, 109)]'Sorting code that follows, must be placed at the top, above, and outside of all remaining procedures***[/COLOR]
Public x&
Public y&
Public z As String
Public bln As Boolean [COLOR=rgb(65, 168, 95)]‘True or false values[/COLOR]

Private Sub [COLOR=rgb(147, 101, 184)]cmbShopOrdNum1[/COLOR]_Click()
bln = Not bln 
Select Case bln

Case 1 
[COLOR=rgb(97, 189, 109)]'Sort ascending[/COLOR]
With[COLOR=rgb(147, 101, 184)] lstMaster[/COLOR]
For x = 0 To .ListCount - 2
For y = x + 1 To .ListCount - 1
If .List(x) > .List(y) Then
z = .List(y)
.List(y) = .List(x)
.List(x) = z
End If
Next y
Next x
End With

Case 0[COLOR=rgb(97, 189, 109)] 
'Sort descending[/COLOR]
With [COLOR=rgb(147, 101, 184)]lstMaster[/COLOR]
For x = 0 To .ListCount - 2
For y = x + 1 To .ListCount - 1
If .List(x) < .List(y) Then
z = .List(y)
.List(y) = .List(x)
.List(x) = z
End If
Next y
Next x
End With
End Select

End Sub
[COLOR=rgb(44, 130, 201)]

The following image shows the results after I click on the 'Shop Order' header (command button). Notice the entire row does not sort as it should.

I would really appreciate the assistance.

 

[John_w]

I entered this code for the first column. I works, the column sorts a to z, then z to a, etc. However, the rest of the column data doesn't sort with it.

Because you need another nested For ... Next loop which performs the swap between the two rows being compared on all the columns, not just one column.

Put this in the userform module:

Private Sub Sort_ListBox(sortColumnIndex As Long, ascendingOrder As Boolean)

    Dim i As Long, j As Long
    Dim temp As Variant
    Dim col As Long
    
    With ListBox1
    
        If ascendingOrder Then
            For i = 0 To .ListCount - 2
                For j = i + 1 To .ListCount - 1
                    If .List(i, sortColumnIndex) > .List(j, sortColumnIndex) Then
                        For col = 0 To .ColumnCount - 1
                            temp = .List(i, col)
                            .List(i, col) = .List(j, col)
                            .List(j, col) = temp
                        Next
                    End If
                Next
            Next
        Else
            For i = 0 To .ListCount - 2
                For j = i + 1 To .ListCount - 1
                    If .List(i, sortColumnIndex) < .List(j, sortColumnIndex) Then
                        For col = 0 To .ColumnCount - 1
                            temp = .List(i, col)
                            .List(i, col) = .List(j, col)
                            .List(j, col) = temp
                        Next
                    End If
                Next
            Next
        End If
    
    End With

End Sub

Call the above routine from each of the command buttons like this, noting how the first argument of Sort_ListBox is the column index (0 being the 1st column, 1 the 2nd column, etc.) of the column to be sorted:

Private Sub CommandButton1_Click()
    Static ascendingOrder As Boolean
    ascendingOrder = Not ascendingOrder
    Sort_ListBox 0, ascendingOrder
End Sub

Private Sub CommandButton2_Click()
    Static ascendingOrder As Boolean
    ascendingOrder = Not ascendingOrder
    Sort_ListBox 1, ascendingOrder
End Sub

 

[Yodelayheewho]

This works absolutely perfectly. Thank you so very, very much!

 

[Yodelayheewho]

Hi @John_w. Per the above post from September of 2022, you helped me with code and at the time it worked. Short story: the sorting is not working properly.
My Master worksheet has 115 columns.
The listbox (lstMaster) on my userform has 9 columns.
For headers, I created nine command buttons that each toggle sort A-Z and Z-A.
When the userform initializes, the listbox columns look fine. However, when I click on a command button, depending upon the column I'm sorting, it sometimes duplicates a couple of rows and leaves a couple rows out. The order is incorrect, etc.
I've been wrestling with this and can't seem to figure out why. I would appreciate the help.

Here is the original code you provided. I've added some notes. I'm still learning, so I have notes and or questions.

Private Sub Sort_ListBox(sortColumnIndex As Long, ascendingOrder As Boolean)
    Dim i As Long
    Dim j As Long
    Dim temp As Variant
    Dim col As Long 'total number of columns
    With lstMaster
        If ascendingOrder Then
            For i = 0 To .ListCount - 2 'why is this minus 2? The listbox has 7 rows (indexed: 0-6) if I subtract 2 this would be row 5, but debug.print shows the result to be 6?
                For j = i + 1 To .ListCount - 1
                    If .List(i, sortColumnIndex) > .List(j, sortColumnIndex) Then
                        For col = 0 To .ColumnCount - 1 'col = 115 because negative 1 displays all available columns
                            temp = .List(i, col)
                            .List(i, col) = .List(j, col)
                            .List(j, col) = temp
                        Next
                    End If
                Next
            Next
        Else 
            For i = 0 To .ListCount - 2
                For j = i + 1 To .ListCount - 1
                    If .List(i, sortColumnIndex) < .List(j, sortColumnIndex) Then '"<" indicated descending order
                        For col = 0 To .ColumnCount - 1
                            temp = .List(i, col)
                            .List(i, col) = .List(j, col)
                            .List(j, col) = temp
                        Next
                    End If
                Next
            Next
        End If
    End With
Debug.Print i, j, col, temp, sortColumnIndex
End Sub

Here are the command button codes.

'Sorts by column index, Suffix is in column 4, so the index = 3. Code 2 OF 2
Private Sub cmbSuffix1_Click()
    Static ascendingOrder As Boolean
    ascendingOrder = Not ascendingOrder
    Sort_ListBox 3, ascendingOrder
End Sub

'Sorts by column index, Shop Order Number is in column 5, so the index = 4. Code 2 OF 2
Private Sub cmbShopOrdNum1_Click()
    Static ascendingOrder As Boolean
    ascendingOrder = Not ascendingOrder
    Sort_ListBox 4, ascendingOrder
End Sub

'Sorts by column index, Proposal is in column 15, so the index = 14. Code 2 OF 2
Private Sub cmbProposal1_Click()
    Static ascendingOrder As Boolean
    ascendingOrder = Not ascendingOrder
    Sort_ListBox 14, ascendingOrder
End Sub
'***COPY CODE TO UPDATE ACTIVE FILE
'Sorts by column index, PO is in column 24, so the index = 23. Code 2 OF 2
Private Sub cmbPO1_Click()
    Static ascendingOrder As Boolean
    ascendingOrder = Not ascendingOrder
    Sort_ListBox 23, ascendingOrder
End Sub

'Sorts by column index, SO is in column 34, so the index = 33. Code 2 OF 2
Private Sub cmbSO1_Click()
    Static ascendingOrder As Boolean
    ascendingOrder = Not ascendingOrder
    Sort_ListBox 33, ascendingOrder
End Sub

'Sorts by column index, Quote is in column 35, so the index = 34. Code 2 OF 2
Private Sub cmbQuote1_Click()
    Static ascendingOrder As Boolean
    ascendingOrder = Not ascendingOrder
    Sort_ListBox 34, ascendingOrder
End Sub

'Sorts by column index, Transfer Order Number is in column 41, so the index = 40. Code 2 OF 2
Private Sub cmbTransOrd1_Click()
    Static ascendingOrder As Boolean
    ascendingOrder = Not ascendingOrder
    Sort_ListBox 40, ascendingOrder
End Sub

'Sorts by column index, Customer Name is in column 88, so the index = 87. Code 2 OF 2
Private Sub cmbCustName1_Click()
    Static ascendingOrder As Boolean
    ascendingOrder = Not ascendingOrder
    Sort_ListBox 87, ascendingOrder
End Sub

'Sorts by column index, End User Name is in column 111, so the index = 110. Code 2 OF 2
Private Sub cmbEUName1_Click()
    Static ascendingOrder As Boolean
    ascendingOrder = Not ascendingOrder
    Sort_ListBox 110, ascendingOrder
End Sub

 

[John_w]

My Master worksheet has 115 columns.
The listbox (lstMaster) on my userform has 9 columns.
For headers, I created nine command buttons that each toggle sort A-Z and Z-A.
When the userform initializes, the listbox columns look fine. However, when I click on a command button, depending upon the column I'm sorting, it sometimes duplicates a couple of rows and leaves a couple rows out. The order is incorrect, etc.
I've been wrestling with this and can't seem to figure out why. I would appreciate the help.

I think the first problem is that you aren't specifying the correct column indexes in the calls to Sort_ListBox, maybe because this part of my previous post could have been clearer:

the first argument of Sort_ListBox is the column index (0 being the 1st column, 1 the 2nd column, etc.) of the column to be sorted

The column indexes I refer to should be the column indexes of the ListBox columns, NOT the sheet columns. So the column index specified in the code for each command button should be 0, 1, 2, 3, etc. respectively.

The second issue is that my Sort_ListBox code operates directly on the ListBox itself, and in my further tests this doesn't seem to work. Use this code instead, which puts the ListBox (lstMaster) values in a 2-d array, sorts the array and assigns the sorted array to the ListBox's List property.

Private Sub Sort_ListBox(sortColumnIndex As Long, ascendingOrder As Boolean)

    Dim LBList As Variant
    Dim i As Long, j As Long
    Dim temp As Variant
    Dim col As Long
  
    LBList = Me.lstMaster.List
  
    If ascendingOrder Then
        For i = LBound(LBList, 1) To UBound(LBList, 1) - 1
            For j = i + 1 To UBound(LBList, 1)
                If LBList(i, sortColumnIndex) > LBList(j, sortColumnIndex) Then
                    For col = LBound(LBList, 2) To UBound(LBList, 2)
                        temp = LBList(i, col)
                        LBList(i, col) = LBList(j, col)
                        LBList(j, col) = temp
                    Next
                End If
            Next
        Next
    Else
        For i = LBound(LBList, 1) To UBound(LBList, 1) - 1
            For j = i + 1 To UBound(LBList, 1)
                If LBList(i, sortColumnIndex) < LBList(j, sortColumnIndex) Then
                    For col = LBound(LBList, 2) To UBound(LBList, 2)
                        temp = LBList(i, col)
                        LBList(i, col) = LBList(j, col)
                        LBList(j, col) = temp
                    Next
                End If
            Next
        Next
    End If
  
    Me.lstMaster.RowSource = ""
    Me.lstMaster.Clear
    Me.lstMaster.List = LBList

End Sub

I'll try to answer your questions/notes in the code.

Dim col As Long 'total number of columns
No, col is a loop counter used by the following For ... Next loop:
For col = 0 To .ColumnCount - 1 'col = 115 because negative 1 displays all available columns
ListBox columns are indexed from 0 to the number of columns (the ColumnCount property) minus 1, hence the above For loop. In your case the ListBox has 9 columns, so col counts from 0 to 8 and at end of the loop it has the value 9, because it has exceeded the end value.

            For i = 0 To .ListCount - 2 'why is this minus 2? The listbox has 7 rows (indexed: 0-6) if I subtract 2 this would be row 5, but debug.print shows the result to be 6?
                For j = i + 1 To .ListCount - 1
                    If .List(i, sortColumnIndex) > .List(j, sortColumnIndex) Then

This is a standard Bubble Sort construct, where the outer loop counts from 0 to the last row but 1 (therefore 0 to ListCount - 2 for a ListBox) and the inner loop counts from the outer loop plus 1 to the last row. If the ListBox has 7 rows, Debug.Print .ListCount - 2 should be 5.

                   If .List(i, sortColumnIndex) < .List(j, sortColumnIndex) Then '"<" indicated descending order

No, the "<" is deciding whether the column value in row i is less than the column value in row j. It is the ascendingOrder As Boolean argument which specifies whether the sort is ascending (ascendingOrder is True) or descending order (ascendingOrder is False).

 

[Yodelayheewho]

@John_w you saved the day! It works perfectly now. Thank you so very much.