none
How to sort Listview In Excel VBA User form with Date RRS feed

  • Question

  • hi All Experts,

    i need to sort data which have been populated from sheet to listview in Userform Base on DATE

    i trying to use below code but unfortunately i received Overflov error:

    Private Sub UserForm_Initialize() Dim Item As ListItem Dim LastLine

    ListView1.View = lvwReport With ListView1.ColumnHeaders .Add Text:="Number" .Add Text:="Date" End With ' Fill some data in listview LastLine= Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To LastLine Set Item = ListView1.ListItems.Add(Text:=Sheets(1).Cells(i, 1)) Item.SubItems(1) = Sheets(1).Cells(i, 2) Next End Sub Private Sub ListView1_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader) Dim Item As ListItem If ColumnHeader.Index = 2 Then For Each Item In ListView1.ListItems Item.SubItems(1) = Format$(Item.ListSubItems(1), "yyyymmdd") Next Item End If With ListView1 .SortKey = ColumnHeader.Index - 1 .Sorted = True If .SortOrder = lvwAscending Then .SortOrder = lvwDescending Else .SortOrder = lvwAscending End If End With If ColumnHeader.Index = 2 Then For Each Item In ListView1.ListItems Item.SubItems(1) = Format$(Item.ListSubItems(1), "yyyy/mm/dd") Next Item End If End Sub

    pls let me have your good ideas to solve this isue

    • Changed type Nima Iranian Monday, December 30, 2013 5:29 AM
    Saturday, December 28, 2013 10:05 AM

Answers

  • finally i could sort this issue out by using below code:

    I was working on this yesterday but unfortunately I ran out of time and had to leave it due to a prior commitment.

    Although you have resolved the issue I thought you might be interested in an alternative solution that I came up with.

    Private Sub ListView1_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)
        'Dim Item As ListItem       'Item is a reserved word; not a good idea to use as variable
        Dim lstItem As ListItem     'Using Prefixes avoids reserved words
        Dim arrSplit
       
        If ColumnHeader.Index = 2 Then
            For Each lstItem In ListView1.ListItems
                    'Including the slashes permits the use of Split function further down.
                    lstItem.SubItems(1) = Format$(lstItem.ListSubItems(1), "yyyy/mm/dd")
            Next lstItem
        End If
      
        With ListView1
            .SortKey = ColumnHeader.Index - 1
            .Sorted = True
            If .SortOrder = lvwAscending Then
                .SortOrder = lvwDescending
            Else
                .SortOrder = lvwAscending
            End If
        End With
       
        If ColumnHeader.Index = 2 Then
            For Each lstItem In ListView1.ListItems
                'Split function creates a zero based array irrespective of option base setting
                arrSplit = Split(lstItem.SubItems(1), "/")
                lstItem.SubItems(1) = Format$(DateSerial(arrSplit(0), arrSplit(1), arrSplit(2)), "dd/mm/yyyy")
            Next lstItem
        End If
       
    End Sub


    Regards, OssieMac


    • Edited by OssieMac Sunday, December 29, 2013 10:45 PM
    • Marked as answer by Nima Iranian Monday, December 30, 2013 5:29 AM
    Sunday, December 29, 2013 10:45 PM
  • finally i could sort this issue out by using below code:

    Private Sub ListView1_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)
    
        Dim Item As ListItem
        Dim i As Integer, j As Integer
        
        If ColumnHeader.Index = 2 Then
            For i = 1 To ListView1.ListItems.Count
                ListView1.ListItems(i).ListSubItems(ColumnHeader.Index - 1).Text = _
                    CDec(CDate(ListView1.ListItems(i).ListSubItems(ColumnHeader.Index - 1).Text))
            Next i
        End If
        
        With ListView1
            .SortKey = ColumnHeader.Index - 1
            .Sorted = True
            If .SortOrder = lvwAscending Then
                .SortOrder = lvwDescending
            Else
                .SortOrder = lvwAscending
            End If
        End With
    
        If ColumnHeader.Index = 2 Then
            For i = 1 To ListView1.ListItems.Count
                ListView1.ListItems(i).ListSubItems(ColumnHeader.Index - 1).Text = _
                    Format(CDate(ListView1.ListItems(i).ListSubItems(ColumnHeader.Index - 1).Text), "DD/MM/YYYY")
            Next i
        End If
    End Sub

    • Marked as answer by Nima Iranian Monday, December 30, 2013 5:29 AM
    Sunday, December 29, 2013 6:49 AM

All replies

  • Try the following. Note the comments.

    Private Sub UserForm_Initialize()
        'Dim Item As ListItem       'Item is a reserved word; not a good idea to use as variable
        Dim lstItem As ListItem     'Using Prefixes avoids reserved words
        Dim LastLine As Long
        Dim i As Long

        ListView1.View = lvwReport

        With ListView1.ColumnHeaders
            .Add Text:="Number"
            .Add Text:="Date"
        End With

        ' Fill some data in listview

        LastLine = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row

        For i = 2 To LastLine
            Set lstItem = ListView1.ListItems.Add(Text:=Sheets(1).Cells(i, 1))
            lstItem.SubItems(1) = Sheets(1).Cells(i, 2)
        Next
    End Sub


    Private Sub ListView1_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)
        'Dim Item As ListItem       'Item is a reserved word; not a good idea to use as variable
        Dim lstItem As ListItem     'Using Prefixes avoids reserved words
       
        If ColumnHeader.Index = 2 Then
            For Each lstItem In ListView1.ListItems
                'After first conversion to Text the formated value is a string that is not
                'recognizable as a date in the yyymmdd format so do not try to convert again.
                If IsDate(lstItem.SubItems(1)) Then
                    'May use format with slashes ("yyyy/mm/dd") in lieu of format used in next line
                    lstItem.SubItems(1) = Format$(lstItem.ListSubItems(1), "yyyymmdd")
                End If
            Next lstItem
        End If
      
        With ListView1
            .SortKey = ColumnHeader.Index - 1
            .Sorted = True
            If .SortOrder = lvwAscending Then
                .SortOrder = lvwDescending
            Else
                .SortOrder = lvwAscending
            End If
        End With
    End Sub


    Regards, OssieMac

    Sunday, December 29, 2013 3:20 AM
  • dear Ossie,

    thanks for your codes that was great.

    but i need to show 2 column as DATE format after sorting.

    is any trick or solution to do this issue??

    Sunday, December 29, 2013 5:26 AM
  • finally i could sort this issue out by using below code:

    Private Sub ListView1_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)
    
        Dim Item As ListItem
        Dim i As Integer, j As Integer
        
        If ColumnHeader.Index = 2 Then
            For i = 1 To ListView1.ListItems.Count
                ListView1.ListItems(i).ListSubItems(ColumnHeader.Index - 1).Text = _
                    CDec(CDate(ListView1.ListItems(i).ListSubItems(ColumnHeader.Index - 1).Text))
            Next i
        End If
        
        With ListView1
            .SortKey = ColumnHeader.Index - 1
            .Sorted = True
            If .SortOrder = lvwAscending Then
                .SortOrder = lvwDescending
            Else
                .SortOrder = lvwAscending
            End If
        End With
    
        If ColumnHeader.Index = 2 Then
            For i = 1 To ListView1.ListItems.Count
                ListView1.ListItems(i).ListSubItems(ColumnHeader.Index - 1).Text = _
                    Format(CDate(ListView1.ListItems(i).ListSubItems(ColumnHeader.Index - 1).Text), "DD/MM/YYYY")
            Next i
        End If
    End Sub

    • Marked as answer by Nima Iranian Monday, December 30, 2013 5:29 AM
    Sunday, December 29, 2013 6:49 AM
  • finally i could sort this issue out by using below code:

    I was working on this yesterday but unfortunately I ran out of time and had to leave it due to a prior commitment.

    Although you have resolved the issue I thought you might be interested in an alternative solution that I came up with.

    Private Sub ListView1_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)
        'Dim Item As ListItem       'Item is a reserved word; not a good idea to use as variable
        Dim lstItem As ListItem     'Using Prefixes avoids reserved words
        Dim arrSplit
       
        If ColumnHeader.Index = 2 Then
            For Each lstItem In ListView1.ListItems
                    'Including the slashes permits the use of Split function further down.
                    lstItem.SubItems(1) = Format$(lstItem.ListSubItems(1), "yyyy/mm/dd")
            Next lstItem
        End If
      
        With ListView1
            .SortKey = ColumnHeader.Index - 1
            .Sorted = True
            If .SortOrder = lvwAscending Then
                .SortOrder = lvwDescending
            Else
                .SortOrder = lvwAscending
            End If
        End With
       
        If ColumnHeader.Index = 2 Then
            For Each lstItem In ListView1.ListItems
                'Split function creates a zero based array irrespective of option base setting
                arrSplit = Split(lstItem.SubItems(1), "/")
                lstItem.SubItems(1) = Format$(DateSerial(arrSplit(0), arrSplit(1), arrSplit(2)), "dd/mm/yyyy")
            Next lstItem
        End If
       
    End Sub


    Regards, OssieMac


    • Edited by OssieMac Sunday, December 29, 2013 10:45 PM
    • Marked as answer by Nima Iranian Monday, December 30, 2013 5:29 AM
    Sunday, December 29, 2013 10:45 PM
  • thank you Ossie,

    your codes is working very well as well as myself but i think my code is more easier to understand and use by other one.

     however thanks for your efforts which showed me right path to solution.

    Monday, December 30, 2013 5:28 AM
  • I'm having a similar problem.   After the sorting of the date, when I try to add more items to my listview, only the first column is added. The other sub items are blank. 

    I can't seem to figure out why.  Sub gets all the data required, its just blank rows

    ' My add Sub

    i = lvDispo.ListItems.Count + 1
    lvDispo.ListItems.Add , , i
    lvDispo.ListItems(i).SubItems(1) = dataType
    lvDispo.ListItems(i).SubItems(2) = Format(strOdate, "yyyy-mm-dd")
    lvDispo.ListItems(i).SubItems(3) = Trim(LCase(strGame))
    lvDispo.ListItems(i).SubItems(4) = Trim(LCase(strStatus))

    lvDispo.Refresh

    'My Header Sort.

        With lvDispo
        If ColumnHeader.Index = 3 Then

            .SortKey = ColumnHeader.Index - 1
            If .SortOrder = lvwAscending Then
                .SortOrder = lvwDescending
            Else
                .SortOrder = lvwAscending
            End If
            .Sorted = True
            End If
        End With

    Any suggestions?

    Wednesday, May 18, 2016 7:52 PM