PDA

View Full Version : [SOLVED] Listview sort when column header clicked



seekon
11-23-2015, 08:24 PM
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
11-23-2015, 11:03 PM
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