Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sorting a ListView

Posted on 2004-10-15
10
Medium Priority
?
567 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
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 750 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

705 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