Solved

Sorting a ListView

Posted on 2004-10-15
10
505 Views
Last Modified: 2008-01-16
The following sub sorts a ListView based on which column you click:

Public Sub SortListView(ByVal lvw As MSComctlLib.ListView, ByVal colHdr As MSComctlLib.ColumnHeader)
   
    lvw.SortKey = colHdr.Index - 1
    lvw.Sorted = True
    lvw.SortOrder = 1 Xor lvw.SortOrder

End Sub

The problem I am having, is that on a certain column, it is only numerical values. I want it to be sorted from greatest value to least, but the system recognizes the first number...for example, the number 2 would appear before 15, because 2 is greater than 1...how do I fix this?
0
Comment
Question by:Produkt
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 32

Expert Comment

by:Erick37
ID: 12320263
It's not easy, but here's one way...

Sorting ListView ListItems Using Callbacks
http://vbnet.mvps.org/index.html?code/callback/lvsortcallback.htm
0
 
LVL 18

Expert Comment

by:JR2003
ID: 12320360
The callbacks method is very slow if there are a large number of items in the list view.

A simple method is to left-pad your numbers with spaces in the listview so that an alphabetic sort actually sort numerically. Just left-pad the numbers when you are adding them to the listView.

e.g. If you know the maximum lenght of a number is 9 then this code would add them so they sort correctly:

ListView1.ListItems.Add ,, String(Abs(9 - Len(iNumber)), " ") & iNumber



Another method is to have a hidden column of zero width that stores the numbers in a format that can be sorted alphabetically and sort on that column instead.

Another method is to store the number that can be sorted alphabetically in the tab of the list item or sub-item. Then swap the tags with the text and do the sort then swap the tags back.

JR
0
 

Author Comment

by:Produkt
ID: 12320605
Would it be effective to use the callback menu if the maximum amount of items in the list is 32?
0
 

Author Comment

by:Produkt
ID: 12320623
I don't know why I said menu...I meant method...
0
 
LVL 19

Expert Comment

by:Shauli
ID: 12320764
Put this sub in a module:

Public Sub SortListView(ByRef oListView As MSComctlLib.ListView, _
                        ByRef oColumnHeader As MSComctlLib.ColumnHeader)
Dim oListItem As MSComctlLib.ListItem
Dim i As Integer
Dim iTempColIndex As Integer
Dim bNoTagInColumn As Boolean
   
    With oListView
        If .ListItems.Count < 2 Then GoTo Exit_Point
            iTempColIndex = .ColumnHeaders.Count - 1
        If oColumnHeader.Index = 1 Then
            For i = 1 To .ListItems.Count
                Set oListItem = .ListItems(i)
                oListItem.ListSubItems(iTempColIndex) = oListItem.Tag
            Next
            If Len(Trim(oListItem.Tag)) = 0 Then bNoTagInColumn = True
        Else
            For i = 1 To .ListItems.Count
                Set oListItem = .ListItems(i)
                oListItem.ListSubItems(iTempColIndex) = oListItem.ListSubItems(oColumnHeader.Index - 1).Tag
            Next
            If Len(Trim(oListItem.ListSubItems(iTempColIndex))) = 0 Then bNoTagInColumn = True
        End If
       
        If bNoTagInColumn Then
            .SortKey = oColumnHeader.Index - 1
        Else
            .SortKey = iTempColIndex
        End If
       
        If .SortOrder = lvwAscending Then
            .SortOrder = lvwDescending
        Else
            .SortOrder = lvwAscending
        End If
       
        For i = 1 To .ListItems.Count
            Set oListItem = .ListItems(i)
            oListItem.ListSubItems(iTempColIndex) = ""
        Next
    End With
   
Exit_Point:
    Set oListItem = Nothing
End Sub

=============
When you load your listview, add a tag to each subitem as follows:
'for numeric sort
YoursubItem.Tag = Format$(!sqlmorder, "000000000000.0000000000")
'for alpha sort
YoursubItem.SubItems(2) = same as to text property
'for dates
YoursubItem.Tag = Format$(!shipdate, "yyyymmddHHMMSS")
'Then add an extra columns at the end
YoursubItem.ListSubItems.Add


===============

In the Listview ColumnClick event do:

Private Sub listRelease_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)
Call SortListView(Listview1, ColumnHeader)   'replace listview1 with your listview name
End Sub

================

S
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 19

Expert Comment

by:Shauli
ID: 12320784
ps. replace "!sqlmorder" and other field names with the data you want to sort. I copied and pasted :)

S
0
 
LVL 4

Expert Comment

by:Glowman
ID: 12320787
Produkt,
I had a similar problem and as irritating as it was it was an easy fix.
Instead of dim'ing as an Int or whatever dim as a string and define the length of the string as such:
    Dim RushYd As String
    Dim RushTd As String
    RushYd = Space$(4)
    RushTd = Space$(2)

Then you can just use your variable to hold your assigned number but it will be sortable numerically.
Hope this helps
G
0
 
LVL 32

Accepted Solution

by:
Erick37 earned 250 total points
ID: 12320982
Here is a versatile method of sorting taken from a sample at CodeGuru.com.  The only requirement is to set the TAG property of the ColumnHeader to flag what kind of data is to be sorted:

'When you setup the listview...
With ListView1
   
        ' Add three columns to the list - one for each data type.
        ' Note that the data type is set in the column header's
        ' tag in each case
   
        .ColumnHeaders.Add(, , "String").Tag = "STRING"
        .ColumnHeaders.Add(, , "Number").Tag = "NUMBER"
        .ColumnHeaders.Add(, , "Date").Tag = "DATE"

'Then the sorting functions:

'****************************************************************
' ListView1_ColumnClick
' Called when a column header is clicked on - sorts the data in
' that column
'----------------------------------------------------------------

Private Sub ListView1_ColumnClick(ByVal ColumnHeader As _
                                    MSComctlLib.ColumnHeader)

    On Error Resume Next
   
    ' Commence sorting
   
    With ListView1
       
        ' Check the data type of the column being sorted,
        ' and act accordingly
       
        Dim l As Long
        Dim strFormat As String
        Dim strData() As String
       
        Dim lngIndex As Long
        lngIndex = ColumnHeader.Index - 1
   
        Select Case UCase$(ColumnHeader.Tag)
        Case "DATE"
       
            ' Sort by date.
           
            strFormat = "YYYYMMDDHhNnSs"
       
            ' Loop through the values in this column. Re-format
            ' the dates so as they can be sorted alphabetically,
            ' having already stored their visible values in the
            ' tag, along with the tag's original value
       
            With .ListItems
                If (lngIndex > 0) Then
                    For l = 1 To .Count
                        With .Item(l).ListSubItems(lngIndex)
                            .Tag = .Text & Chr$(0) & .Tag
                            If IsDate(.Text) Then
                                .Text = Format(CDate(.Text), _
                                                    strFormat)
                            Else
                                .Text = ""
                            End If
                        End With
                    Next l
                Else
                    For l = 1 To .Count
                        With .Item(l)
                            .Tag = .Text & Chr$(0) & .Tag
                            If IsDate(.Text) Then
                                .Text = Format(CDate(.Text), _
                                                    strFormat)
                            Else
                                .Text = ""
                            End If
                        End With
                    Next l
                End If
            End With
           
            ' Sort the list alphabetically by this column
           
            .SortOrder = (.SortOrder + 1) Mod 2
            .SortKey = ColumnHeader.Index - 1
            .Sorted = True
           
            ' Restore the previous values to the 'cells' in this
            ' column of the list from the tags, and also restore
            ' the tags to their original values
           
            With .ListItems
                If (lngIndex > 0) Then
                    For l = 1 To .Count
                        With .Item(l).ListSubItems(lngIndex)
                            strData = Split(.Tag, Chr$(0))
                            .Text = strData(0)
                            .Tag = strData(1)
                        End With
                    Next l
                Else
                    For l = 1 To .Count
                        With .Item(l)
                            strData = Split(.Tag, Chr$(0))
                            .Text = strData(0)
                            .Tag = strData(1)
                        End With
                    Next l
                End If
            End With
           
        Case "NUMBER"
       
            ' Sort Numerically
       
            strFormat = String(30, "0") & "." & String(30, "0")
       
            ' Loop through the values in this column. Re-format the values so as they
            ' can be sorted alphabetically, having already stored their visible
            ' values in the tag, along with the tag's original value
       
            With .ListItems
                If (lngIndex > 0) Then
                    For l = 1 To .Count
                        With .Item(l).ListSubItems(lngIndex)
                            .Tag = .Text & Chr$(0) & .Tag
                            If IsNumeric(.Text) Then
                                If CDbl(.Text) >= 0 Then
                                    .Text = Format(CDbl(.Text), _
                                        strFormat)
                                Else
                                    .Text = "&" & InvNumber( _
                                        Format(0 - CDbl(.Text), _
                                        strFormat))
                                End If
                            Else
                                .Text = ""
                            End If
                        End With
                    Next l
                Else
                    For l = 1 To .Count
                        With .Item(l)
                            .Tag = .Text & Chr$(0) & .Tag
                            If IsNumeric(.Text) Then
                                If CDbl(.Text) >= 0 Then
                                    .Text = Format(CDbl(.Text), _
                                        strFormat)
                                Else
                                    .Text = "&" & InvNumber( _
                                        Format(0 - CDbl(.Text), _
                                        strFormat))
                                End If
                            Else
                                .Text = ""
                            End If
                        End With
                    Next l
                End If
            End With
           
            ' Sort the list alphabetically by this column
           
            .SortOrder = (.SortOrder + 1) Mod 2
            .SortKey = ColumnHeader.Index - 1
            .Sorted = True
           
            ' Restore the previous values to the 'cells' in this
            ' column of the list from the tags, and also restore
            ' the tags to their original values
           
            With .ListItems
                If (lngIndex > 0) Then
                    For l = 1 To .Count
                        With .Item(l).ListSubItems(lngIndex)
                            strData = Split(.Tag, Chr$(0))
                            .Text = strData(0)
                            .Tag = strData(1)
                        End With
                    Next l
                Else
                    For l = 1 To .Count
                        With .Item(l)
                            strData = Split(.Tag, Chr$(0))
                            .Text = strData(0)
                            .Tag = strData(1)
                        End With
                    Next l
                End If
            End With
       
        Case Else   ' Assume sort by string
           
            ' Sort alphabetically. This is the only sort provided
            ' by the MS ListView control (at this time), and as
            ' such we don't really need to do much here
       
            .SortOrder = (.SortOrder + 1) Mod 2
            .SortKey = ColumnHeader.Index - 1
            .Sorted = True
           
        End Select
   
    End With
   
End Sub

'****************************************************************
' InvNumber
' Function used to enable negative numbers to be sorted
' alphabetically by switching the characters
'----------------------------------------------------------------

Private Function InvNumber(ByVal Number As String) As String
    Static i As Integer
    For i = 1 To Len(Number)
        Select Case Mid$(Number, i, 1)
        Case "-": Mid$(Number, i, 1) = " "
        Case "0": Mid$(Number, i, 1) = "9"
        Case "1": Mid$(Number, i, 1) = "8"
        Case "2": Mid$(Number, i, 1) = "7"
        Case "3": Mid$(Number, i, 1) = "6"
        Case "4": Mid$(Number, i, 1) = "5"
        Case "5": Mid$(Number, i, 1) = "4"
        Case "6": Mid$(Number, i, 1) = "3"
        Case "7": Mid$(Number, i, 1) = "2"
        Case "8": Mid$(Number, i, 1) = "1"
        Case "9": Mid$(Number, i, 1) = "0"
        End Select
    Next
    InvNumber = Number
End Function

'****************************************************************
'
reference:
http://www.codeguru.com/vb/controls/vb_listview/article.php/c1669/
0
 
LVL 18

Expert Comment

by:JR2003
ID: 12322093
>>>> Would it be effective to use the callback method if the maximum amount of items in the list is 32?

32's fine!
It get's slow if there are a few hundred items in the listview.
0
 
LVL 32

Expert Comment

by:Erick37
ID: 12322160
Right, with only 32 items I would suggest the code with the fewest lines because speed will not be an issue.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now