Listview sort when column header clicked

[seekon]

Hi,

I am trying to add code to a listview control in an excel userform, where if a user clicks the column header, that it sorts this column. This code should allow every column to be sorted. I thought this would be available in the properties, but sorting by column header does not appear to be an option that is available.


I finally found this code that appears maybe what I am looking for, but I receive a compile error "user- defined type not defined" which may simply mean that I have referenece this code in my form incorrectly, not sure how to reference this code correctly.

Private Sub ListView1_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)
If ListView1.ListItems.Count = 0 Then Exit Sub


 'Public Sub SortListView(ListViewObject As ListView, _
                        ' ByVal ColumnHeader As MSComctlLib.ColumnHeader)
 '**********************************************************************
 'Purpose      : Sort list view items
 'Input        : ListView object, Selected column
 '               ListViewObject : ListView object
 '               ColumnHeader   : Selected column
 'Output       : -
 'Return value : -
 'Date         : 5.8.2014
 'Author       : Lapot
 'Code     :
 'Modification : -
 '**********************************************************************
     
     Dim strItem As String               'List item
     Dim intMaxItemLength As Integer     'Max. length of list items
     Dim intCounter As Integer           'Counter
     
     With ListView1
         'Set sorted property of listview
         .Sorted = False
         'Add temporary column to sort
         .ColumnHeaders.Add , , , 0
     
         'Select first item from list view
         'If first column
         If ColumnHeader.Index = 1 Then
             strItem = .ListItems(1)
             'If another column
         Else
             strItem = .ListItems(1).SubItems(ColumnHeader.Index - 1)
         End If
     End With
     
     'Date type?
     If IsDate(strItem) Then
         With ListView1
             'If first column
             If ColumnHeader.Index = 1 Then
                 For intCounter = 1 To .ListItems.Count
                     'Get list item
                     strItem = Trim(.ListItems(intCounter))
                     'Add list item to last column in new format to sort
                     .ListItems(intCounter).SubItems(.ColumnHeaders.Count - 1) = Format(strItem, "yyyymmddHHMMSS")
                 Next
             'If another column
             Else
                 For intCounter = 1 To .ListItems.Count
                     'Get sub item
                     strItem = Trim(.ListItems(intCounter).SubItems(ColumnHeader.Index - 1))
                     'Add sub item to last column in new format to sort
                     .ListItems(intCounter).SubItems(.ColumnHeaders.Count - 1) = Format(strItem, "yyyymmddHHMMSS")
                 Next
             End If
             'Set sort key property (temporary column)
             .SortKey = .ColumnHeaders.Count - 1
         End With
     'Numberic type?
     ElseIf IsNumeric(strItem) Then
         With ListView1
             'Set start up value for maximum length of items
             intMaxItemLength = 0
             'If first column
             If ColumnHeader.Index = 1 Then
                 'Find max. length of items
                 For intCounter = 1 To .ListItems.Count
                     strItem = Trim(Replace(Replace(.ListItems(intCounter), ",", ""), ".", ""))
                     If Len(strItem) > intMaxItemLength Then
                         intMaxItemLength = Len(strItem)
                     End If
                 Next
                 For intCounter = 1 To .ListItems.Count
                     'Get sub item
                     strItem = Trim(Replace(Replace(.ListItems(intCounter), ",", ""), ".", ""))
                     'Add sub item to last column in new format to sort
                     .ListItems(intCounter).SubItems(.ColumnHeaders.Count - 1) = "0" & String(intMaxItemLength - Len(strItem), "0") & strItem
                 Next
             'If another column
             Else
                 'Find max. length of items
                 For intCounter = 1 To .ListItems.Count
                     strItem = Trim(Replace(Replace(.ListItems(intCounter).SubItems(ColumnHeader.Index - 1), ",", ""), ".", ""))
                     If Len(strItem) > intMaxItemLength Then
                         intMaxItemLength = Len(strItem)
                     End If
                 Next
                 For intCounter = 1 To .ListItems.Count
                     'Get sub item
                     strItem = Trim(Replace(Replace(.ListItems(intCounter).SubItems(ColumnHeader.Index - 1), ",", ""), ".", ""))
                     'Add sub item to last column in new format to sort
                     .ListItems(intCounter).SubItems(.ColumnHeaders.Count - 1) = "0" & String(intMaxItemLength - Len(strItem), "0") & strItem
                 Next
             End If
             'Set sort key property (temporary column)
             .SortKey = .ColumnHeaders.Count - 1
         End With
     'Another type?
     Else
         ListView1.SortKey = ColumnHeader.Index - 1
     End If
     
     'Set properties of list view
    With ListView1
         'Change the sort order
         .SortOrder = 1 - .SortOrder
         'Set sorted property
         .Sorted = True
         'Remove temporary column
         .ColumnHeaders.Remove .ColumnHeaders.Count
     End With
     
 End Sub

[seekon]

wow, it appears I was able to just use the following code to achieve what i wanted:

Private Sub ListView1_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)


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