Sort Userform ListBox

V

vmjamshad

New Member
Joined
Jan 5, 2017
Messages
16
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.
Code: 
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

 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
Try the following code, which needs to be placed in the userform code module (change the name of the option buttons accordingly)...

Code: 
[FONT=Courier New][COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] OptionButton1_Click()
    [COLOR=darkblue]Call[/COLOR] SortListBox(0) [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(2) [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])
    [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] 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]
    [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] vData(i, SortByCol) > vData(j, SortByCol) [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
                [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]

Also, while it won't make any real difference, I would nevertheless suggest using the List property to assign the items to your listbox, instead of the AddItem method. The AddItem method adds a row containing 10 columns. So when transferring the contents of the listbox to a variant array, as in my posted example, the array gets assigned 10 columns, with 4 columns containing data, and 6 columns containing no data. And so when looping through the columns, it unnecessarily loops through 6 columns. Therefore, I would suggest the following...

Code: 
[FONT=Courier New][COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] UserForm_Initialize()

    [COLOR=darkblue]Dim[/COLOR] aData() [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] LstRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/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]With[/COLOR] ListBox1
        .ColumnCount = 4
        .ColumnWidths = "130;30;30;130"
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    LstRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    [COLOR=darkblue]ReDim[/COLOR] aData(1 [COLOR=darkblue]To[/COLOR] LstRow - 1, 1 [COLOR=darkblue]To[/COLOR] 4)
    
    j = 1
    [COLOR=darkblue]For[/COLOR] i = 2 [COLOR=darkblue]To[/COLOR] LstRow
        aData(j, 1) = Cells(i, 4)
        aData(j, 2) = Cells(i, 1)
        aData(j, 3) = Cells(i, 3)
        aData(j, 4) = Cells(i, 2)
        j = j + 1
    [COLOR=darkblue]Next[/COLOR] i
    
    Me.ListBox1.List = aData
     
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR][/FONT]

Hope this helps!
 
Upvote 0
Domenic said:
Also, while it won't make any real difference, I would nevertheless suggest using the List property to assign the items to your listbox, instead of the AddItem method. The AddItem method adds a row containing 10 columns. So when transferring the contents of the listbox to a variant array, as in my posted example, the array gets assigned 10 columns, with 4 columns containing data, and 6 columns containing no data. And so when looping through the columns, it unnecessarily loops through 6 columns. Therefore, I would suggest the following...

Code: 
[FONT=Courier New][COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] UserForm_Initialize()

[COLOR=#ff0000][B]    Dim aData() As Variant
[/B][/COLOR]    [COLOR=darkblue]Dim[/COLOR] LstRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
[COLOR=#ff0000][B]    Dim i As Long
    Dim j As Long
[/B][/COLOR]     
    [COLOR=darkblue]With[/COLOR] ListBox1
        .ColumnCount = 4
        .ColumnWidths = "130;30;30;130"
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    [COLOR=#008000][B]LstRow = Cells(Rows.Count, 1).End(xlUp).Row[/B][/COLOR]
    
[COLOR=#ff0000][B]    ReDim aData(1 To LstRow - 1, 1 To 4)
    
    j = 1
    For i = 2 To LstRow
        aData(j, 1) = Cells(i, 4)
        aData(j, 2) = Cells(i, 1)
        aData(j, 3) = Cells(i, 3)
        aData(j, 4) = Cells(i, 2)
        j = j + 1
    Next i
    
    Me.ListBox1.List = aData
[/B][/COLOR]     
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR][/FONT]
Click to expand...
If we move the green highlighted line of code above the With statement, then everything I highlighted in red above can be replaced by a single line of code...
Code: 
[TABLE="width: 500"]
<tbody>[TR]
[TD]Private Sub UserForm_Initialize()
  Dim LstRow As Long
[COLOR=#008000][B]  LstRow = Cells(Rows.Count, 1).End(xlUp).Row[/B][/COLOR]
  With ListBox1
    .ColumnCount = 4
    .ColumnWidths = "130;30;30;130"
[COLOR=#ff0000][B]    .List = Application.Index(Range("A1:D" & LstRow).Value, Evaluate("ROW(1:" & LstRow & ")"), Split("4 1 2 3"))[/B][/COLOR]
  End With
End Sub[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Thanks, this is what i was looking for. Adding to that, here we have selected two columns to sort. is there a way to sort one in descending order.
 
Upvote 0
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.

Code: 
[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]
 
Upvote 0
Domenic said:
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.

Code: 
[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]
Click to expand...
If you don't mind adding a temporary worksheet to the work book, you can let Excel do all the work for you...
Code: 
[table="width: 500"]
[tr]
	[td]Private Sub OptionButton1_Click()
    Call SortListBox(SortByCol:=1, SortOrder:=xlAscending) '0 = first column
End Sub

Private Sub OptionButton2_Click()
    Call SortListBox(SortByCol:=3, SortOrder:=xlDescending) '2 = third column
End Sub

Private Sub SortListBox(SortByCol As Long, SortOrder As XlSortOrder)
  Application.ScreenUpdating = False
  With Sheets.Add
    With .Range("A1").Resize(ListBox1.ListCount, 4)
      .Cells = ListBox1.List
      .Sort .Columns(SortByCol), SortOrder
    End With
    ListBox1.List = .Range("A1:D" & .Cells(Rows.Count, "A").End(xlUp).Row).Value
    Application.DisplayAlerts = False
    .Delete
    Application.DisplayAlerts = True
  End With
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]
Note 1: With my code, the SortByCol argument needs to be passed the actual column number (not one less like in your code).

Note 2: My code assumes (hard-codes) the number of columns to be 4.

Note 3: Of course, I used the code I posted in Message #4 for the UserForm_Initialize event.:LOL:
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
Filter data in listbox based on three conditions by selected from combobox
Replies
1
Views
155
tubrak
T
C
  • Question
Excel Userform Listbox Search issues
Replies
3
Views
102
Akuini
Akuini
P
  • Question
Listbox Columns - re-arrange order shown
Replies
2
Views
159
Pacman52
P
S
  • Solved
Show the largest date and the smallest date on the listbox Userform
Replies
5
Views
123
Domenic
D
A
  • Solved
Exclude columns in listbox on userform except first two columns & last column
2
Replies
16
Views
331
abdelfattah
A

Forum statistics

Threads
1,214,591
Messages
6,120,426
Members
448,961
Latest member
nzskater

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