Sorting a ListView on Numeric Data?

BJ · Oct 19th, 2000, 09:54 AM

I have found two different codes to do this but I can't get it to work for me. I have tried to right align my strings. But when I do my varible goes to nothing.

Can someone help my right align my data so it will sort.

Sub ShowFolderList(folderspec)
Dim FolderScript, MainFolder, SubFolder, User, CurrentDir
Dim Size As String
Dim SizeTrim As Single

Set FolderScript = CreateObject("Scripting.FileSystemObject")
Set MainFolder = FolderScript.GetFolder(folderspec)
Set CurrentDir = MainFolder.SubFolders
For Each SubFolder In CurrentDir
DoEvents

On Error Resume Next
User = SubFolder.Name
Size = SubFolder.Size / 1052613.06
SizeTrim = Format(Size, "#,##0")

If Size > Text1.Text Then
ListView1.Font.Bold = True
Else
ListView1.Font.Bold = False
End If

ListView1.ListItems.Add 1, , User
ListView1.ListItems(1).ListSubItems.Add 1, , Right(CStr(SizeTrim) & String(4, " "), 4)
User = User & vbCrLf
Size = "N/A"
Next

End Sub

At the end ListView1.ListItems(1).ListSubItems.Add1, it adds nothing. But the varible is "SizeTrim" still there.

**sebs** · Oct 19th, 2000, 10:27 AM

HeSaidJoe gave me one once and it works great

Code:

'CREATE SORTING OF LISTVIEW BY ALPHA, NUMBER, DATE
'
'PUT THIS IN GEN DECLARATIONS OF FORM
'*****************************************************************
'
Private Declare Function LockWindowUpdate Lib "user32" _
        (ByVal hWndLock As Long) As Long
'
Private Function InvNumber(ByVal Number As String) As String
    'This function is to deal with the negative numbers, so then they can be sorted alphabetically
'
    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
'
'LOAD THE LISTVIEW WITH RANDOM INFORMATION
'PUT THIS IN LOAD OR CLICK
'*********************************************************
'
Dim l As Long
    Dim dblRnd As Double
    Dim dteRnd As Date
    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"
'
        ' Set the column alignment - has no bearing on the sorts.
'
        .ColumnHeaders(1).Alignment = lvwColumnLeft
        .ColumnHeaders(2).Alignment = lvwColumnRight
        .ColumnHeaders(3).Alignment = lvwColumnCenter
'
' Set BorderStyle property.
   ListView1.BorderStyle = ccFixedSingle
'
' Set View property to Report.
   ListView1.View = lvwReport
'
        ' Populate the list with data
        With .ListItems
            For l = 1 To 100
                With .Add(, , "ListItem " & Format(l, "0000"))
                    dblRnd = (Rnd() * 10000) - 5000
                    dteRnd = (Rnd() * 1000) + Date
                    .ListSubItems.Add , , Format(dblRnd, "0.00")
                    .ListSubItems.Add , , Format(dteRnd, _
                    "dd/mm/yyyy")
                End With
            Next l
        End With
    End With
'
'PUT THIS IN THE COLUMNCLICK EVENT OF LISTVIEW
'***********************************************
    Dim lngStart As Long
    Dim lngCursor As Long
    Dim l As Long
    Dim strFormat As String
    Dim strData() As String
    Dim lngIndex As Long
    On Error Resume Next
    
    With ListView1
        ' Display the hourglass cursor while list is sorting
        lngCursor = .MousePointer
        .MousePointer = vbHourglass
        
        ' Prevent the ListView control from updating on screen
        ' and also to speed up the sort
        LockWindowUpdate .hWnd
        
        ' Check the data type of the column being sorted,
        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,
                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")
                'Re-format the values so as they
                ' can be sorted alphabetically.
                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
                'Just sort it alphabetically
                .SortOrder = (.SortOrder + 1) Mod 2
                .SortKey = ColumnHeader.Index - 1
                .Sorted = True
        End Select
        ' Unlock the list window so that the OCX can update it
        LockWindowUpdate 0&
        ' Restore the previous cursor
        .MousePointer = lngCursor
    End With

BJ · Oct 19th, 2000, 10:49 AM

This is some of the code I tried. I get a varible not define error for every ColumnHeader.(anything).

Thanks
Brandon

**sebs** · Oct 19th, 2000, 11:07 AM

It's suppose to work :

In your listview, the columheader that your numeric value is in,be sure to put is tag to number.

BJ · Oct 19th, 2000, 11:23 AM

The code I put in the click event that says
' Check the data type of the column being sorted,
lngIndex = ColumnHeader.Index - 1

is where I get the error "Varible not defined" - ColumnHeader

The other code that puts my info in seems to work fine.

Thanks
Brandon

**sebs** · Oct 19th, 2000, 11:52 AM

Post your code that i gave you with your error!

Cuz if you declare the lngIndex, i don't know it gives you that error!!!!

BJ · Oct 23rd, 2000, 02:36 PM

Here is my code, sorry I can't make it look good like you guys do. Like I said it gives me and error everytime I click on the Header bars. That says varible not defined. Anywhere it says columnheader.(anything).

Private Sub ListView1_ColumnClick(ByVal space As MSComctlLib.ColumnHeader)
Dim lngStart As Long
Dim lngCursor As Long
Dim l As Long
Dim strFormat As String
Dim strData() As String
Dim lngIndex As Long
On Error Resume Next

With ListView1
' Display the hourglass cursor while list is sorting
lngCursor = .MousePointer
.MousePointer = vbHourglass

' Prevent the ListView control from updating on screen
' and also to speed up the sort
LockWindowUpdate .hWnd

' Check the data type of the column being sorted,
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,
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")
'Re-format the values so as they
' can be sorted alphabetically.
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
'Just sort it alphabetically
.SortOrder = (.SortOrder + 1) Mod 2
.SortKey = ColumnHeader.Index - 1
.Sorted = True
End Select
' Unlock the list window so that the OCX can update it
LockWindowUpdate 0&
' Restore the previous cursor
.MousePointer = lngCursor
End With
End Sub

Thanks
Brandon

**sebs** · Oct 24th, 2000, 07:10 AM

I think thats the problem:

Private Sub ListView1_ColumnClick(ByVal space As MSComctlLib.ColumnHeader)

instead of columnheader, use space.index-1

And go look here to make your thread more fancy!!

BJ · Oct 24th, 2000, 08:02 AM

Thanks Again.

Brandon

Thread: Sorting a ListView on Numeric Data?

Thread Tools

Display

i have one

Posting Permissions