Toggle sort multicolumn listbox with headers as command buttons

Y

Yodelayheewho

New Member
Joined
Jun 23, 2017
Messages
13
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...

VBA Code: 
[/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.

1663275952090.png


I would really appreciate the assistance.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Yodelayheewho said:
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.
Click to expand...
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:
VBA Code: 
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:
VBA Code: 
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
 
Upvote 0
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.

VBA Code: 
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.

VBA Code: 
'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
 
Upvote 0
Yodelayheewho said:
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.
Click to expand...

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:

John_w said:
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
Click to expand...

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.

VBA Code: 
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.

VBA Code: 
            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.

VBA Code: 
                   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).
 
Last edited:
Upvote 0
Solution
John_w said:
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 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.

VBA Code: 
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.

VBA Code: 
            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.

VBA Code: 
                   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).
Click to expand...
This is great...however I am not using command buttons as headers to sort but rather a drop down of column names to choose. Therefore, how can I have the headers show up after sorting using the above method? I am new to listboxes
 
Upvote 0
mrfitness_79 said:
This is great...however I am not using command buttons as headers to sort but rather a drop down of column names to choose. Therefore, how can I have the headers show up after sorting using the above method? I am new to listboxes
Click to expand...

With the drop down (ComboBox) named ComboBox1 and its items in the same order as the ListBox columns:
VBA Code: 
Private Sub ComboBox1_Change()
    Sort_ListBox Me.ComboBox1.ListIndex, ascendingOrder:=True
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Listbox on userform to show results in reverse order
2
Replies
16
Views
361
Fuji
F
C
  • Question Question
Searching using TextBox and ListBox
Replies
1
Views
279
campbell1093
C
G
  • Question Question
Move the selected item up one position in the list Userform ListBox
Replies
4
Views
98
giri u
G
D
  • Solved
Comparing characters in two strings and looking for a difference
Replies
2
Views
90
dgparryuk
D
K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
242
krunal123
K

Forum statistics

Threads
1,224,847
Messages
6,181,325
Members
453,032
Latest member
Pauh

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top