How do I sort My Listbox in a Userform?

[Marian_Montes]

Hi there,

I have a question for my expert friends here in the forums.

How do I sort my Data in a Listbox within a Userform in ascending & Descending order using a Command Button?

Thanks in advance,
Marian

 

[jindon]

Hi

How are you populating the list for the ListBox?

 

[Marian_Montes]

I'm populating it via AddItem.

 

[jindon]

Commandbutton1 to sort ascending
commandbutton2 to sot descending

Private Sub CommandButton1_Click()
Dim x
x = Me.ListBox1.List
ReDim Preserve x(UBound(x), 0)
QuicksortA x, LBound(x), UBound(x), 0
Me.ListBox1.List = x
End Sub

Private Sub CommandButton2_Click()
Dim x
x = Me.ListBox1.List
ReDim Preserve x(UBound(x), 0)
QuicksortD x, LBound(x), UBound(x), 0
Me.ListBox1.List = x
End Sub

Private Sub QuicksortA(ary, LB, UB, ref)
    Dim M As Variant, temp
    Dim i As Long, ii As Long, iii As Integer
    i = UB
    ii = LB
    M = UCase(ary(Int((LB + UB) / 2), ref))
    Do While ii <= i
        Do While UCase(ary(ii, ref)) < M
            ii = ii + 1
        Loop
        Do While UCase(ary(i, ref)) > M
            i = i - 1
        Loop
        If ii <= i Then
            For iii = LBound(ary, 2) To UBound(ary, 2)
                temp = ary(ii, iii): ary(ii, iii) = ary(i, iii)
                ary(i, iii) = temp
            Next
            ii = ii + 1: i = i - 1
        End If
    Loop
    If LB < i Then QuicksortA ary, LB, i, ref
    If ii < UB Then QuicksortA ary, ii, UB, ref
End Sub

Private Sub QuicksortD(ary, LB, UB, ref)
    Dim M As Variant, temp
    Dim i As Long, ii As Long, iii As Integer
    i = UB
    ii = LB
    M = UCase(ary(Int((LB + UB) / 2), ref))
    Do While ii <= i
        Do While UCase(ary(ii, ref)) > M
            ii = ii + 1
        Loop
        Do While UCase(ary(i, ref)) < M
            i = i - 1
        Loop
        If ii <= i Then
            For iii = LBound(ary, 2) To UBound(ary, 2)
                temp = ary(ii, iii): ary(ii, iii) = ary(i, iii)
                ary(i, iii) = temp
            Next
            ii = ii + 1: i = i - 1
        End If
    Loop
    If LB > i Then QuicksortA ary, LB, i, ref
    If ii > UB Then QuicksortA ary, ii, UB, ref
End Sub

 

[Tom Urtis]

In addition to jindon's contribution:

If your userform's ListBox1 is being populated with AddItem, which means its RowSource property is cleared as it should be, then here is code for sorting the ListBox items.

In the userform module, place these statements at the top, above and outside of all procedures:

Option Explicit
Public x&
Public y&
Public z As Variant
Public bln As Boolean

Now let's say your ListBox is being populated like this in the Initialize event (all this code would go into the userform's module):

Private Sub UserForm_Initialize()
With ListBox1
.AddItem "Mary"
.AddItem "Bill"
.AddItem "Zelda"
.AddItem "William"
.AddItem "Tom"
.AddItem "Mike"
.AddItem "Jim"
.AddItem "Bob"
End With
End Sub


Here are two options for sorting:

(1)
Assign the sort code to its own command button click event, where two command buttons are being used, one for sort ascending, and the other for sort descending, example:

Private Sub CommandButton1_Click()
'Sort ascending
With ListBox1
For x = 0 To .ListCount - 2
For y = x + 1 To .ListCount - 1
If .List(x) > .List Then
z = .List
.List = .List(x)
.List(x) = z
End If
Next y
Next x
End With
End Sub

Private Sub CommandButton2_Click()
'Sort descending
With ListBox1
For x = 0 To .ListCount - 2
For y = x + 1 To .ListCount - 1
If .List(x) < .List Then
z = .List
.List = .List(x)
.List(x) = z
End If
Next y
Next x
End With
End Sub



(2)
The other option is to use one command button and "toggle sort" with just that button like this for example. I include this because you wrote:
"in ascending & Descending order using a Command Button" ("a" being singular so that's why the toggle code here if that's what you meant).
Notice this also changes the command button's caption accordingly as a convenience to the user:

Private Sub CommandButton3_Click()
bln = Not bln
Select Case bln

Case 1
'Sort ascending
With ListBox1
For x = 0 To .ListCount - 2
For y = x + 1 To .ListCount - 1
If .List(x) > .List Then
z = .List
.List = .List(x)
.List(x) = z
End If
Next y
Next x
End With
CommandButton3.Caption = "Sort descending"

Case 0
'Sort descending
With ListBox1
For x = 0 To .ListCount - 2
For y = x + 1 To .ListCount - 1
If .List(x) < .List Then
z = .List
.List = .List(x)
.List(x) = z
End If
Next y
Next x
End With
CommandButton3.Caption = "Sort ascending"
End Select

End Sub

 

[Marian_Montes]

Thank you so much for Helping. Best regards to you all. (",?
-Marian