Sort Userform ListBox

[vmjamshad]

Dear All,

My Data has 4 columns with the following headings:
Heading 1,Heading 2,Heading 3 and Heading 4.

I have created a Listbox using the below code. In the user from i have 2 Option Buttons which indicates 2 columns. I want to sort my list box based on the selection. Example: if the select the 'serial number' option box, it should filter based on the first column value (Heading 1)and if i choose 'job' it should filter based on the third column value (Heading 3)

Can anybody help me to find a code for that.

Please see below the code for Listbox.

Private Sub UserForm_Initialize() 
     
    With ListBox1 
        .ColumnCount = 4 
        .ColumnWidths = "130;30;30;130" 
    End With 
     
    LstRow = Cells(Rows.Count, 1).End(xlUp).Row 
     
    For a = 0 To LstRow - 2 
        b = a + 2 
        ListBox1.AddItem 
        ListBox1.list(a, 0) = Cells(b, 4) 
        ListBox1.list(a, 1) = Cells(b, 1) 
        ListBox1.list(a, 2) = Cells(b, 3) 
        ListBox1.list(a, 3) = Cells(b, 2) 
         
    Next a 
     
End Sub

 

[vmjamshad]

You haven't specified which column you want to sort in descending order. In any case, I've changed SortListBox to accept another parameter to determine whether to sort in ascending order or descending order. Change the SortOrder argument for the Click events accordingly.

[FONT=Courier New][COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] OptionButton1_Click()
    [COLOR=darkblue]Call[/COLOR] SortListBox(SortByCol:=0, SortOrder:=xlAscending) [COLOR=green]'0 = first column[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] OptionButton2_Click()
    [COLOR=darkblue]Call[/COLOR] SortListBox(SortByCol:=2, SortOrder:=xlDescending) [COLOR=green]'2 = third column[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] SortListBox(SortByCol [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], SortOrder [COLOR=darkblue]As[/COLOR] XlSortOrder)
    [COLOR=darkblue]Dim[/COLOR] vData [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] vTemp [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] bSwap [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Boolean[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] j [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] k [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    bSwap = [COLOR=darkblue]False[/COLOR]
    [COLOR=darkblue]With[/COLOR] Me.ListBox1
        vData = .List
        [COLOR=darkblue]For[/COLOR] i = [COLOR=darkblue]LBound[/COLOR](vData, 1) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](vData, 1) - 1
            [COLOR=darkblue]For[/COLOR] j = i + 1 [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](vData, 1)
                [COLOR=darkblue]If[/COLOR] SortOrder = xlAscending [COLOR=darkblue]Then[/COLOR]
                    [COLOR=darkblue]If[/COLOR] vData(i, SortByCol) > vData(j, SortByCol) [COLOR=darkblue]Then[/COLOR]
                        bSwap = [COLOR=darkblue]True[/COLOR]
                    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
                [COLOR=darkblue]Else[/COLOR]
                    [COLOR=darkblue]If[/COLOR] vData(i, SortByCol) < vData(j, SortByCol) [COLOR=darkblue]Then[/COLOR]
                        bSwap = [COLOR=darkblue]True[/COLOR]
                    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
                [COLOR=darkblue]If[/COLOR] bSwap [COLOR=darkblue]Then[/COLOR]
                    [COLOR=darkblue]For[/COLOR] k = [COLOR=darkblue]LBound[/COLOR](vData, 2) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](vData, 2)
                        vTemp = vData(i, k)
                        vData(i, k) = vData(j, k)
                        vData(j, k) = vTemp
                    [COLOR=darkblue]Next[/COLOR] k
                    bSwap = [COLOR=darkblue]False[/COLOR]
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
            [COLOR=darkblue]Next[/COLOR] j
        [COLOR=darkblue]Next[/COLOR] i
        .List = vData
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR][/FONT]

Hi Domenic, How do I sort if the values are percentages? I think the percentages are not sorting here as integer.
And all the eight columns are not percentages. Few are integer, I have string also. Could you please help me to find a solution? Thanks J

 

[Domenic]

Percentages should be sorted as well, unless they're being recognized as text values instead of numerical values. You can easily test whether this is the case by using the ISNUMBER function. For example, let's say that cell D2 contains a percentage value, the following should return TRUE if its a numerical value, and FALSE if it's a text value...

=ISNUMBER(D2)

If the percentages are in fact being recognized as text values, you can easily convert them to numerical values by doing the following...

1) Select an empty cell.

2) Copy the empty cell (right-click the cell, and select Copy).

3) Select the range of cells containing the percentages.

3) Right-click the selected range of cells, select PasteSpecial, select Add under Operation, and then click on OK.

Hope this helps!

 

[chipsworld]

Hope someone sees this since it is old, but...

I love this code, works except...

If I select the first row of data to import into my user form, it will not import. Works on all other lines. Regardless of which column I sort by, the first line doesn't work.

Any ideas?

Private Sub SortListBox(SortByCol As Long)
    Dim vData As Variant
    Dim vTemp As Variant
    Dim i As Long
    Dim j As Long
    Dim k As Long
    With Me.ListBox1
        vData = .List
        For i = LBound(vData, 1) To UBound(vData, 1) - 1
            For j = i + 1 To UBound(vData, 1)
                If vData(i, SortByCol) < vData(j, SortByCol) Then
                    For k = LBound(vData, 2) To UBound(vData, 2)
                        vTemp = vData(i, k)
                        vData(i, k) = vData(j, k)
                        vData(j, k) = vTemp
                    Next k
                End If
            Next j
        Next i
        .List = vData
    End With
End Sub

 

[chipsworld]

Never mind...problem was in a different section of code. All good now