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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,040
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,426
Messages
5,528,690
Members
409,830
Latest member
KT50

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top