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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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

K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
192
krunal123
K
I
  • Question Question
Select Listbox value to be then taken to customer on worksheet
Replies
1
Views
104
ipbr21054
I
I
  • Question Question
Run time erorr 94 when selecting value in Listbox
Replies
4
Views
99
ipbr21054
I
C
  • Question Question
Searching using TextBox and ListBox
Replies
1
Views
231
campbell1093
C
S
  • Question Question
Edit listbox values including columns and update the worksheet
Replies
0
Views
286
spanner28
S

Forum statistics

Threads
1,223,909
Messages
6,175,314
Members
452,634
Latest member
cpostell

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