Column Header Sort On Userform

[Molden]

Hi,

I'm trying to sort my data so when you click the column header the data either goes Ascending or Descending. What I have below works (ish) but the data is currency and rather sorting it like this:

£1.23
£2.22
£3.44
£10.04
£24.11
£30.00

It only looks at the 1st number i.e:

£1.23
£10.04
£2.22
£24.11
£3.44
£30.00

The code I am using is below - any help would be appriciated.

Thanks

Michael

Private Sub lvwMain_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)

If ColumnHeader.Text = "Remaining Balance" Then

Me.lvwMain.Sorted = True
Me.lvwMain.SortKey = 3

If Me.lvwMain.SortOrder = lvwAscending Then
Me.lvwMain.SortOrder = lvwDescending
Else
Me.lvwMain.SortOrder = lvwAscending
End If

Else

Me.lvwMain.Sorted = False

End If

End Sub

 

[Norie]

Molden

I'm afraid you can't sort numerically using the Sort method.

You could write code to do the sort for you but it might be fairly complicated.

 

[Hermanito]

I've encountered similar issues and as a (dirty) workaround I make sure I format the numbers with trailing zeroes before putting them into the Listview... then it sorts correctly.
That option sure is easier than writing your own sort code, as Norie already said, but you have to be able to live with the extra zeroes...

 

[Norie]

Hermanito

I actually tried to write some code for it.

Here's what I came up with

Option Explicit
 
Private Sub CommandButton6_Click()
Dim lstItem As ListItem
Dim lstNextItem As ListItem
Dim tmpItem As String
Dim I As Long
Dim J As Long
Dim K As Long
Dim NoSubItems As Long
 
    For J = 1 To lvAgreements.ListItems.Count - 1

        For I = 1 To lvAgreements.ListItems.Count - 1
 
            Set lstItem = lvAgreements.ListItems(I)

            NoSubItems = lstItem.ListSubItems.Count

            Set lstNextItem = lvAgreements.ListItems(I + 1)
 
            If Val(lstNextItem.SubItems(2)) > (lstItem.SubItems(2)) Then

                ReDim arrItems(0 To NoSubItems)
 
                tmpItem = lstItem

                lstItem = lstNextItem

                lstNextItem = tmpItem

                For K = 1 To NoSubItems
                    tmpItem = lstItem.SubItems(K)
                    lstItem.SubItems(K) = lstNextItem.SubItems(K)
                    lstNextItem.SubItems(K) = tmpItem
                Next K

            End If

        Next I
 
    Next J
 
End Sub

This was for a listview with 2 subitems for each listitems.

It sorts descending by the 2 subitem, which is a value without currency symbol.

Just tried removing the currency symbol:

            If Val(Replace(lstNextItem.SubItems(2), "£", "")) > Val(Replace(lstItem.SubItems(2), "£", "")) Then

Now just need to remember and save the file.

 

[Hermanito]

Have you tested that code on a listview with a large number of items? I often use one particular listview here, which can contain easily up to 3000 elements with about 10 subitems... not sure how your code would perform on that.

The trick with leading zeroes (I said trailing before, I meant leading of course!!) and using the built-in sort method is still quite fast in that case...

 

[Norie]

No, I've hardly tested it at all and I'm sure the sort method is pretty inefficient.

It works with the data I had to hand and it's pretty rough code.

There's some harcoding in it, eg the subitem/header to base the sort on, and probably some other errors too.

 

[Norie]

Tested with 1000 items and it toolk 39 secs.

Obviously unreasonable.

I'm sure there are quicker methods, perhaps a disconnected recordset or even just sorting the underlying data in situ or using temporary sheet.

You'd have to repopulate the listview with either.

 

[Hermanito]

Ouch!

Nice try though...

I vaguely recall having implemented my own sort routine in a distant past, also for a listview, I guess I now remember why I don't reuse that code anymore

 

[Norie]

Hermanito

The Bubblesort I used is a pretty inefficient way of sorting, it's also one of the simplest.

I'm sure there's better ways, perhaps even some 'trick' that allows you to sort numerically (without adding extra 0s).