Sorting a Listview Column
ListView is modified version of List box in VBA or VB6 user form. Using this control we will be able to display data in a compact and presentable table format. When the data is displayed, users might want to sort some data based on specific column.
But this option is not enabled by default. You have to add this below code to enable sort when user click on a column header.
Usually, Listview.Sorted option can be enabled in the control properties in design view. This will sort the column that has the sortkey specified. That means, it will sort only the default column. Users cannot pick their column on runtime.
ListView Column Sort on click
Copy paste this code to your Listview userform code. Run the code and click on any column that has the data. You will be able to see that column gets sorted. And also, the order gets changed from descending to ascending and vise versa.
Private Sub ListViewName_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader) With ListViewName 'Column Index starts from 0 .SortKey = ColumnHeader.Index - 1 'Toggle sort order to Ascending/Descending If .SortOrder = lvwAscending Then .SortOrder = lvwDescending Else .SortOrder = lvwAscending End If 'Sort values .Sorted = True End With End Sub
The sorting happens by considering the data as text or string data type. If any column has numeric data, it will still be considered as text. So, you have to be careful with what data you are planning to sort.
In the above code, replace the ‘ListViewName’ with your actual list view control name. This name can be found in the form design. Click on Listview control and you can get the control name in its properties section that is displayed in the left hand side panel.