Sorting a listbox

Roderick_E

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
I found this code and it works great to sort by the first column of my 4 column list box but I want the open to sort by the other columns. I will have the user click the label above the column so I only need to know what to change to indicate sorting by column 1 2 3 (default column 0 as listbox columns start with 0)

Code:
Private Sub Label3_Click() ' sort list box by quote number
 'Sorts ListBox List
     Dim i As Long, j As Long, x As Long, sTemp As String
    With mainform.searchbox
        For j = LBound(.List) To UBound(.List) - 1 Step 1
            For i = LBound(.List) To UBound(.List) - 1 Step 1
                If .List(i) > .List(i + 1) Then
                    [COLOR=#ff0000]For x = 0 To (.ColumnCount - 1) Step 1
[/COLOR]                        sTemp = .List(i, x)
                        .List(i, x) = .List(i + 1, x)
                        .List(i + 1, x) = sTemp
                    Next x
                End If
            Next i
        Next j
    End With
End Sub

I thought changing the zero in red would do it, but nope
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,142
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
i hope am right about this :) Try

Since the second argument in .List is left out, it assumes column 1, so i have included the second argument to be P. So P=1 is 2nd column, P=2, 3rd column.....

o all you need to be able to do is return an integer depending on which label was clicked(select case maybe) and feed that into the code
Rich (BB code):
Private Sub Label3_Click() ' sort list box by quote number
 'Sorts ListBox List
     Dim i As Long, j As Long, x As Long, sTemp As String, P As Long
     P = 1 'sorts using column 2
    With mainform.searchbox
        For j = LBound(.List) To UBound(.List) - 1 Step 1
            For i = LBound(.List) To UBound(.List) - 1 Step 1
                If .List(i, P) > .List(i + 1, P) Then
                    For x = 0 To (.ColumnCount - 1) Step 1
                        sTemp = .List(i, x)
                        .List(i, x) = .List(i + 1, x)
                        .List(i + 1, x) = sTemp
                    Next x
                End If
            Next i
        Next j
    End With
End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,652
Messages
5,838,597
Members
430,557
Latest member
MK15

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
Top