Suggestions Sample File
ListView


1. What is a ListView ?
1.2 The ListView's purpose
1.3 ListView background

2. Create a ListView
2.1 Userform
2.1.1 Design Mode
2.1.2 Loaded Userform
2.1.3 Sample file
2.2 Worksheet
2.2.1 ListView clearing
2.2.2 ListView showing
2.2.3 ListView position
2.2.4 Sample file
2.3 RAM

3. Creating columnheaders
3.1 Real-time
3.2 Design Mode
3.3 VBA design

4. ListItems
4.1 Adding
4.2 ListSubItems: adding
4.3 Fill by Array
4.4 Fill by Listobject
4.5 ListView size

5. ListItem read and modify
5.1 ListItem read
5.2 ListItem modify
5.3 ListSubItem read
5.4 ListSubItem modify
5.5 ListSubItem direct access
5.6 ListSubItem modify directly

6 Delete elements
6.1 ListItem
6.2 All ListItems
6.3 ListSubItem
6.4 All ListSubItems

7. Icons in ListView
7.1 Columnheader
7.2 ListItem
7.2.1 Remove from ListItem
7.3 ListSubItem
7.3.1 Remove from ListSubItem

8. ListView Views
8.1 Icons
8.2 Small icons
8.3 List
8.4 Report

9. Sort
9.1 by date
9.2 by number
9.3 Interactive sort
9.4 Interactive by date
9.5 Interactive numerically

10 ListView: data storage

1. What is a ListView ?

A ListView in VBA is a collection object: you can store strings in it
The Listbox exists of 'records' (rows) and 'fields' (columns).
The file list in Explorer is such a ListView.


VBA has several other Objects to store data:
- a dictionary
- a collection
- an array variable
- an ActiveX ComboBox
- an ActiveX ListBox
- a ListBox in a Userform
- a sortedlist

The choice for an Object is dependent of the goal you want to accomplish.
I will not compare all these Objects here.
An introduction to the ListView is the primary goal.

A ListView can be part of a userform, a worksheet as ActiveX-object or completely in the internal memory.
You are very familiar with the ListView since it is the main window structure that Window Explorer uses to show folders, files and their properties.

A ListView has 4 distinctive features.
You can sort its items by every 'property'.
The ListView can present data in 4 distinctive 'views'.
You can add graphical elements to elements in the ListView.
You can refer to every element in the ListView by its key.

1.2 The purpose of a ListView

You can use the ListView to store data and to adapt them efficiently.
By means of a ListView the data are available in the Random Access Memory.
Since no calculations have to be performed, nor screens refreshed it's faster than an Excel Worksheet, Word document or Powerpoint Slide.

1.3 The origin of the ListView

The Listview isn't part of the VBA-library (Visual Basic for Applications), nor the Excel-VBA-library (Microsoft Excel x.0 Object Library) nor the 'Userform'-library (Microsoft Forms 2.0 Object Library).
The ListView is part of the Windows controls library (Microsoft Windows Common Controls 6.0 (SP6)).
This library isn't loaded by default.
You can load this library in the VBEditor, tab 'Tools', option 'references'.
You will find it in the alphabetical list of libraries.
Mark the library to load it.
In VBA you can load the library in 2 different ways: as GUID or as file.
GUID
ThisWorkbook.VBProject.References.AddFromGuid "{831FDD16-0C5C-11D2-A9FC-0000F8754DA1}", 0, 2
File
ThisWorkbook.VBProject.References.AddFromFile Replace(Environ("comspec"), "cmd.exe", "MSCOMCTL.OCX")
If your system lacks this file you can load it here Listbox_file.

2. Create a ListView

You can create 3 kinds of Listviews:
- as userform control in a Userform
- as Active-X control in an Excel worksheet
- as virtual Object in Random Access Memory

2.1 ListView in Userform

A ListView in a Userform is always empty: it doesn't contain any data.
In this respect the ListView is identical to a ComboBox or a ListBox in a Userform.
Use the Design Mode to add the Listview to the Userform.
All Listview properties you assign in Design Mode will be stored in the Userform's design.

2.1.1 ListView in Design Mode

In Design Mode use the 'Toolbox' to add a ListView.
If the Toolbox doesn't contain the Listview you can add it:
- put the cursor in the Toolbox
- right-click
- click additional controls ..
- select/check: Microsoft ListView Control, version 6.0
After you have added the ListView to the userform, you can set a large number of properties.
This is useful if you know in advance which data you want to include in the ListView.
In the property ColumnHeaders you can predefine the size (number of columns) of the ListView and possibly the column names.
All properties can be set in advance.(it seems in Excel versions >14.0 it can't)
Filling the ListView with data can only be done when the Userform is loaded.

2.1.2 ListView in loaded Userform

To add a ListView in a loaded Userform the reference isn't required, you can use 'late binding'.
Private Sub Userform_Initialize()
Controls.Add "MSComctlLib.ListViewCtrl.2", "snb"
End Sub
The ListView's position by default is the left upper corner of the Userform.
To create and position the ListView simultaneously:
Private Sub Userform_Initialize()
With Controls.Add("MSComctlLib.ListViewCtrl.2", "snb")
.Top = 70
.Left = 20
.Height = 90
.Width = 120
End With
End Sub

2.1.3 ListView in sample file

The sample file contains a Userform.
The Userform_Initialize event contains all code to show the Listview correctly.
But it's preferable to set all properties in Design Mode.
VBA-code exclusively can populate the ListView and add icons to elements.
The sample file illustrates the use of icons in ColumnHeaders, ListItems and ListSubitems.
It also contains code to switch between View options by clicking in the Userform.
The event _ColumnClick sorts the columns ascendingly / descendingly.

2.2 ListView in worksheet

You can add a ListView as ActiveX-control to a worksheet.
The first argument (Class Type) in the method 'Add' is "MSComctlLib.ListViewCtrl.2".
All other arguments are optional.
Sheet1.OLEObjects.Add "MSComctlLib.ListViewCtrl.2"
The last four arguments determine the position and size of the ListView: Left, Top, Width, Height.
To set the Listview's position and size:
Sheet1.OLEObjects.Add("MSComctlLib.ListViewCtrl.2", , , , , , , columns(6).left, rows(1).top, 300, 300).name = "snb"

2.2.1 Clearing a ListView in a worksheet

In Excel 2010 the Listview will not be stored in a stored workbook.
If you want to use the ListView you will have to rebuild it every time the workbook is loaded.
The Listview's OLEobject-container however is being kept, the ListView-object itself isn't.
In order to be able to create a new Listview the OLEobject-container has to be removed first.
If the Listview's name isn't known beforehand:
On Error Resume Next
For Each it In Sheet1.OLEObjects
x3 = TypeName(it.Object) = "ListView"
If Err.Number <> 0 Then it.Delete
Err.Clear
Next
If the ListView's name is know:
Private Sub Workbook_Open()
On Error Resume Next
Sheet1.OLEObjects("LV_00").Delete
End Sub

2.2.2 Show the ListView in the worksheet

In Excel 2010 the ListView isn't visible if added in VBA; only the OLEObject container is being shown.
You need a trick to overcome this: the switch between worksheets and to scroll the worksheet, using PgUp and PgDn.
You need 2 worksheets to do this.
In VBA
Sheet1.OLEObjects.Add("MSComctlLib.ListViewCtrl.2", , , , , , , columns(6).left, rows(1).top, 300, 300).name = "snb"

Sheet2.Activate
Sheet1.Activate
ActiveWindow.LargeScroll 1
ActiveWindow.LargeScroll , 1

2.2.3 ListView position

A ListView is part of an OLE object.
The OLEObject is part of the 'Shapes' collection in a sheet.
A 'Shape' is automatically linked to a cell's position and size.
That is not very practical for a ListView.
Set the property .placement = 3 to use the Listview as an independent object in the sheet.
With Sheet1.OLEObjects.Add("MSComctlLib.ListViewCtrl.2", , , , , , , columns(6).left, rows(1).top, 300, 300)
.Name = "snb"
.Placement=3
End With

Sheet2.Activate
Sheet1.Activate
ActiveWindow.LargeScroll 1
ActiveWindow.LargeScroll , 1

2.2.4 ListView in sample file

The sample file to this page contains the code in the event procedure Workbook_Open to create a Listview in a sheet.
All elements that are visible in the Userform will also be visible in the worksheet.
The code in 'Workbook_open' also creates the sorting code of the _ColumnClick event in the sheet's macromodule.

2.3 ListView in memory

In this case you need to use the CLSID (ClassIdentification).
To create a virtual ListView in memory use:
With GetObject("New:{996BF5E0-8044-4650-ADEB-0B013914E99C}")
.....
End With

3. Columnheaders

A ListView consists of 'Records' with 'Fields'.
To show 'Fields' you have to add columnheaders.
Columnheader are independent of the amount of fields.
- a ListView can contain more columnheaders than the number of fields in a record.
- a ListView can contain fewer columnheaders than the number of fields in a record.

You need columheaders in order:
- to show the ListView's reportview
- to sort the data in the ListView
- to use the ListItem's property .Subitems

Show/hide separate columns

The object 'ColumnHeader' in the ListView has no property .Visible.
You can show/hide a column using its property .Width.
If the property .Width has value 0, the column isn't visible in reportview (.View = 3)

Show/hide columnheaders

You can show/hide columnheaders in reportview.
The sorting in the Listview is independent of the showing/hiding of columnheaders.

Show all columnheaders
.HideColumnHeaders =False
Hide all columnheaders
.HideColumnHeaders =True

3.1 Creating columnheaders in real-time

The method to create columheaders has 4 arguments
- index; the order of the column from left to right
- key; how to refer directly to the columnheader
- caption: the visible text in the columnheader
- width: the columnwidth in reportview (.View = 3).
if you omit the width argument, VBA calculates the columnwidth itself, based on the Listview's width and the number of columns
You only have to specify the widths if you want them different from the calculated ones.

To set the width for columns 1,2,3 and 4 in ListView 'LV_00' to 60,30,90 and 60 respectively.
With LV_00
for jj = 1 to 4
.ColumnHeaders.Add , "K_" & jj,"Kolom " & jj, choose(jj, 60, 30, 90, 60)
next
.HideColumnHeaders = False
end with

3.2 Creating columnheaders in design mode

The Design Mode offers the opportunity to create/set columnheaders.
It looks as if this opportunity has been removed in versions > Office 2010
Click in the window 'Properties' the second option 'Custom'.
Activate the tab 'Column Headers'.

3.3 Save the columnheaders design in VBA

To set the columnheaders properties in VBA:
Sub M_snb()
With ThisWorkbook.VBProject.VBComponents("Userform1").Designer
For jj = 1 To 4
.Controls("LV_00").ColumnHeaders.Add , "K_" & jj, "kolom " & jj, choose(jj, 60, 30, 90, 60)
Next
End With
End Sub
After this you can alter any property e.g.
Private Sub Userform_Initialize()
For jj = 1 To 4
LV_00.ColumnHeaders(jj).Text = "Room " & jj
Next
End Sub

4. ListItems


4.1 ListItems Add

The 'records' in a Listview are called 'ListItems' in VBA.
A ListView is a collection of ListItems.
The 'fields' of the records are called 'ListSubItems' in VBA.
Every record is a collection of ListSubItems.
To add a record you use: .ListItems.Add
The first ListItem has index number 1.

The method 'ListItems.Add' has 5 arguments
- the record's order position in the ListItem collection ( = index number)
if you omit this argument, the index number is the number of items + 1 .
- the unique key for the record; optional
- the text/caption of the record; optional
- the linked icon; optional
- the linked small icon; optional

For the use of icons see 7. Icons in ListView.
Add several records:
Private Sub Userform_Initialize()
For j = 1 To 4
LV_00.ListItems.Add , "Sleutel_" & j , "Text " & j
Next
End Sub
Each key of a ListItem must be unique.
If you do not use keys you can add as many identical ListItems as you wish.
In that case the index number is the exclusive identifier.

There is no method to check the existence of a Listitem's key (like .exists in a Dictionary).

4.2 ListSubItems Add

To add a field (ListSubitem) in a record (Listitem) use .ListSubItems.Add
The first ListSubItem gets index number 1.
The method 'ListSubItems.Add' has 5 arguments
- the field's position by index number
if omitted the ListSubItem gets the last index number + 1
- a unique key
- the field's text/content
- a link to a ReportIcon in the reportview (.View = 3)
For the use of icons see 7. Icons in ListView.
- a tooltip text, that is being shown when you hover over the field
Private Sub Userform_Initialize()
With LV_00
For j = 1 To 4
With .ListItems.Add( , "Key_" & j , "Text " & j)
For jj=1 to 6
.ListSubItems.Add ,"Keys_" & jj),"Text " & jj, ,,"tooltiptext_" & jj
next
Next
End With
End Sub
Each key of a ListSubItem in a record must be unique.
If you do not use keys you can add as many identical ListSubItems as you wish.
In that case the index number is the exclusive identifier.

There is no method to check the existence of a ListSubItem's key (like .exists in a Dictionary).

4.3 ListView fill by Array

In a lot of cases you want to fill the ListView by worksheet data.
If you take the first worksheet row as columnheaders:
Private Sub Userform_Initialize()
sn = Sheet1.Cells(1).CurrentRegion

With LV_00
For jj = 1 To UBound(sn, 2)
.ColumnHeaders.Add , , sn(1, jj)
Next
.HideColumnHeaders = False

For j = 2 To UBound(sn)
With .ListItems.Add(, , sn(j, 1))
For jj = 2 To UBound(sn, 2)
.ListSubItems.Add , , sn(j, jj)
Next
End With
Next
End With
End Sub

4.4 ListView fill by ListObject

If the worksheet contains a ListObject ('Table') you can use:
Private Sub Userform_Initialize()
sp = Sheet1.ListObjects(1).HeaderRowRange
sn = Sheet1.ListObjects(1).DataBodyRange

With LV_00
For jj = 1 To UBound(sp, 2)
.ColumnHeaders.Add , , sp(1, jj)
Next
.HideColumnHeaders = False

For j = 2 To UBound(sn)
With .ListItems.Add(, , sn(j, 1))
For jj = 2 To UBound(sn, 2)
.ListSubItems.Add , , sn(j, jj)
Next
End With
Next
End With
End Sub

4.5 The size of a ListView

The ListView has no property to return the number of records in the ListView.
You can use the collection of ListItems.
Msgbox LV_00.ListItems.count
The amount of fields per record can vary.
The number of SubListItems isn't suited as indicator for the number of 'fields'.
The number of columnheaders indicates the maximum number of columns that can be shown in reportview.
Msgbox LV_00.ColumnHeaders.Count

5 ListItem: read and adapt


5.1 ListItem read properties

Each ListItem has properties such as e.g.:
- index number
- key
- text
- linked icon
- linked small icon
You can read these properties per ListItem.
for each it in LV_00.ListItems
MsgBox join(array(it.Index,it.Key, it.Text,it.Icon,it.SmallIcon, vbLf)
next

5.2 ListItem: adapt properties

The ListItem's adaptable properties:
- key
- text
- linked icon
- linked small icon
With LV_00.ListItems("Rob")
it.Key = "New_Key"
it.Text = "New text"
it.Icon = "peer"
it.SmallIcon = "appel"
End With

5.3 ListSubItem: read properties

Each ListSubItem has properties such as e.g.:
- index number
- key
- text
- linked icon
- tooltiptext
You can read these properties per ListSubItem.
for each it in LV_00.ListItems("Rob").ListSubItems
MsgBox join(array(it.Index,it.Key, it.Text,it.ReportIcon,it.ToolTipText, vbLf)
next

5.4 ListSubItem: adapt properties

The ListSubItem adaptable properties:
- key
- text
- linked icon
- tooltiptext

Code to adapt these properties:
With LV_00.ListItems("Rob").ListSubItems(1)
.Key = "New_Fieldkey"
.Text = "New field text"
.ReportIcon = "peer"
.ToolTipText = "tooltip_3"
End With

5.5 ListSubItem: read contents

You can assign a key to every ListItem.
You can also assign a key to every ListSubItem.

Use these 2 keys to read every single ListSubItem in the ListView.
MsgBox LV_00.ListItems("Rob").ListSubItems("Department")
If keys are absent you can use the index numbers.
In that case you can apply the property .SubItems or the collection .ListSubItems
For j = 1 To LV_00.ListItems("Rob").ListSubItems.Count
msgbox LV_00.ListItems("Rob").ListSubItems(j)
msgbox LV_00.ListItems("Rob").SubItems(j)
Next
A For Each ... Next loop is only applicable to the collection .ListSubItems.
For each it in LV_00.ListItems("Rob").ListSubItems
msgbox it
Next

5.6 ListSubItem: adapt content

You can assign a key to every ListItem.
You can also assign a key to every ListSubItem.

Use these 2 keys to adapt any single ListSubItem in the ListView. Change a ListSubItem by adapting its property .Text.
LV_00.ListItems("Rob").ListSubItems("Department").Text = "Warehouse"
If keys are absent you can use the index numbers.
In that case you can apply the property .SubItems or the collection .ListSubItems.
For j = 1 To LV_00.ListItems("Rob").ListSubItems.Count
LV_00.ListItems("Rob").ListSubItems(j).Text = "new time"
Next
Or use a For Each ... Next loop.
For each it in LV_00.ListItems("Rob").ListSubItems
it.Text = it.Text & " new"
Next

6 Delete Elements

6.1 ListItem: delete

You can delete a ListItem using de method .Remove.
The argument 'index' indicates which ListItem should be deleted.
The first ListItem has index number 1

- If the index number is known, you can enter it directly
- if the index number is unknown, retrieve it by the ListItem's key
With LV_00.ListItems
.Remove 3
.Remove LV_00.ListItems("Rob").Index
End With

6.2 Delete all ListItems

The method .Clear deletes all ListItems in the ListView.
With LV_00.ListItems
.Clear
End With

6.3 ListSubItem: delete

You can delete a ListSubItem using the method .Remove.
The first argument indicates by index number which ListSubItem should be deleted.
The first ListSubItem has index number 1

- If the index number is known, you can enter it directly
- if the index number is unknown, retrieve it by the ListSubItem's key
With LV_00. ListItems("Rob").ListSubItems
.Remove 3
.Remove LV_00.ListItems("Rob").ListSubItems("Department").Index
End With

6.4 ListSubItems: delete all

The method .Clear deletes all ListSubItems in a certain ListItem ('Record').

With LV_00.ListItems("Rob")
.Clear
End With

7. Icons in the ListView

3 Listview elements can contain icons:
- the ColumnHeader
- a ListItem
- a ListSubItem

These elements are being shown by its icon and/or text.
Icon are not part of the ListView itself.
Icons are being stored in separate 'ImageLists' in the Userform or the Worksheet.
The ImageList is part of the same library that contains the ListView.

Of course this is not applicable to a virtual (invisible) ListView.

The ListView distinguishes between Icons, SmallIcons and ReportIcons.
You need a separate Imagelist for Icons, SmallIcons and ReportIcons since each list can only contain 1 size of images.

In order to make use of Icons every ListView element - ColumnHeader, ListItem or ListSubItem needs to be linked to an Imagelist.
An Imagelist can be part of a Userform or of a worksheet; it doesn't matter where.

7.1 Icons in column header

Columnheaders are only visible in the ListView's reportview.
The use of icons in ColumnHeaders makes only sense if
- the reportview is active (.View = 3)
- the columnheaders are visible ( .HideColumnHeaders = False )

To link icons to columnheaders, a link to an imagelist must be established by the .ColumHeaderIcons property.
If the ListView name in the Userform is LV_00 and the ImageList name in the Userform IL_00:
LV_00.ColumnHeaderIcons = IL_00
When the Imagelist has been linked you can assign an icon to each separate ColumnHeader.
That is happening in the property .Icon of .ColumnHeader.
Each columnheader can have its own, or no icon.

Use the icon's name in the linked Imagelist.

a new columnheader
LV_00.ColumnHeaders.Add , "Key_1", "Text_1", 120, "apple"
an existing columnheader
LV_00.ColumnHeaders("Key_").Icon ="apple"
Use the icon's index number in the linked Imagelist

a new columnheader
LV_00.ColumnHeaders.Add , "Key_1", "Tex_1", 120, 2
an existing columnheader
LV_00.ColumnHeaders("Key_1").Icon = 2
The columnheader height adapts itself automatically to the icon sizes in the linked ImageList.

7.2 Icons in a ListItem

ListItems can contain normal or small icons.
To show normal icons set the ListView property .View = 0.
To show small icons: .View = 1.

If you want to use normal and small icons you need 2 separate ImageLists, each containing icons of different sizes.
You will have to link these InmageLists by 2 different ListView properties: .Icons and .SmallIcons
Link 'normal' icons to ListItems.
Assume the ListView LV_00 and the ImageList with 'normal' icons IL_00:
LV_00.Icons = IL_00
Link 'small' icons to ListItems.
Assume the ListView LV_00 and the ImageList with 'small' icons IL_01:
LV_00.SmallIcons = IL_01
The next step is to link each ListItem to an icon.
In the ListItems' property .Icon you link to a 'normal' icon.
In the ListItem's property .SamllIcon you link to a small icon.
The .property .Icon is independent of the property .SmallIcon.

Every icon in an Imagelist has a unique index number and a unique key.
The text in the .Icon property refers to the key in the linked Imagelist.
The text in the .SmallIcon property refers to the key in the linked Imagelist.

The icon's key in the Imagelist.

A new ListItem
LV_00.ListItems.Add , "key_1", "text_1", "apple", "apple_small"
An exisiting ListItem
LV_00.ListItems("key_1").Icon ="apple"
LV_00.ListItems("key_1").SmallIcon = "apple_small"
The icon's index number.

Every icon in an Imagelist has a unique index number and a unique key.
A number in the property .Icon refers to the icon's index number in the linked Imagelist.
A number in the property .SmallIcon refers to the icon's index number in the linked Imagelist.

A new ListItem
LV_00.ListItems.Add, "sleutel 1", "text 1", 3, 7
Existing ListItem
LV_00.ListItems("key 1").Icon = 3
LV_00.ListItems("key 1").SmallIcon = 7

7.2.1 Delete the ListItem icon

To delete the 'normal' icon:
LV_00.ListItems("sleutel 1").Icon = Empty
To delete the small icon
LV_00.ListItems("sleutel 1").SmallIcon = Empty
To conditionally assign an icon to a ListItem with the IIf-method:

LV_00.ListItems.Add , "key_1", "text_1", iif(LV_00.ListItems.Count mod 2 =0,"apple",Empty)
To conditionally assign a small icon to a ListItem with the IIf-method:

LV_00.ListItems.Add , "key_1", "text_1", , iif(LV_00.ListItems.Count <5,"apple_small",Empty)

7.3 Icons in a ListSubItem

ListSubItems ('fields') in a ListItem can contain icons.
Only the reportview (.View=3) of the Listview shows them.
No distinction is made between Icons and SmallIcons.
The ListSubItem uses the Imagelist linked in the Listview's .SmallIcons property.
In this Imagelist you can determine the size of it's icons.

Assume Listview (LV_00) and ImageList (IL_01):
LV_00.SmallIcons = IL_01
The property .Reportitem of the ListSubItam contains the key or index number of an icon in the linked ImageList.

A new ListSubItem: key
LV_00.ListItems("Rob").ListSubItems.Add , "Key_1", "text_1", "apple", "tooltiptex_1"
An existing ListSubItem
LV_00.ListItems("Rob").ListSubItems.ReportIcon = "apple"
A new ListSubItem: index number
LV_00.ListItems("Rob").ListSubItems.Add , "key_1", "text_1", 3, "tooltiptext_1"
An existing ListSubItem: index number
LV_00.ListItems("Rob").ListSubItems.ReportIcon = 3

7.3.1 Delete icon from ListSubItem

Change the reference to the icon in the property .ReportIcon; replace it by the value Empty.
LV_00.ListItems("key_1").ListSubItems(1).ReportIcon = Empty
If the icon is conditional and you use the method IIf, you also need the value 'Empty'.

In the .ReportIcon argument:
LV_00.ListItems("Rob").ListSubItems.Add , "key_1", "text_1", IIf(LV_00.ListItems.Count>4, 3, Empty), "tooltiptext_1"

8 ListView views

The ListView has 4 views.
The ListItems are visible in all views.
Only in report-view all ListSubItems ('fields') and columnheaders are visible.

The property .View controls how ListItems are being shown.

8.1 ListView View: Icons

This ListView.View property has the value lvwIcon or 0.

The ListItems are being sorted from the left upper cell to the right bottom cell.
The ListItems icons are visible dependent of:
- the property ListView.Icons if the Listview is linked to an ImageList
- and the property .ListItem(j).Icon of every single ListItem.
The property .Icons makes a link to an ImageList.
This link is only relevant for this View option lvwIcon.
In all other cases the Imagelist linked to the property .SmallIcons is decisive.

8.2 ListView View: small Icons

The ListView.View property has value lvwSmallIcon or 1.

The ListItems are being sorted from the left upper cell to the right bottom cell.
The ListItems small icons are visible dependent of:
- the property ListView.SmallIcons to link the Listview to an ImageList
- and the property .ListItem(j).SmallIcon value (key or index number) of every single ListItem.

8.3 ListView View: list

The ListView.View property has value lvwList or 2.

ListItems are being sorted from top to bottom and from left to right in columns.

Small icons are visible dependent of:
- the property ListView.SmallIcons to link the Listview to an ImageList
- and the property .ListItem(j).SmallIcon value (key or index number) of every single ListItem

8.4 ListView View: report

The ListView.View property has value lvwReport or 3.

Information is being shown as a table.

columnheaders
- all texts can be visible
- all icons can be visible

ListItems
- all texts can be visible
- all icons can be visible

ListSubItems
- all texts can be visible.
- all icons can be visible


Which elements are visible is dependent of

Column
- the column property .Width is greater than 0

Columnheader
- the property ListView.HideColumnHeaders is False

Columnheader Icon
- the property ListView.ColumnHeaderIcons is linked to an ImageList
- the property columnheader(n).Icon contains an index number or Key referring to the linked Imagelist

ListItem text
- the corresponding ColumnHeader is visible

ListItem icon:
- the corresponding ColumnHeader is visible
- the property ListView.SmallIcons is linked to a valid Imagelist
- the property ListItems(j).SmallIcon contains a valid key or valid index number

ListSubItem Text:
- the corresponding ColumnHeader is visible

ListSubItem icon:
- the corresponding ColumnHeader is visible
- the property ListView.SmallIcons is linked to a valid Imagelist
- the property ListSubItems(j).ReprotIcon contains a valid key or valid index number

9. Sorting

The ListView has it's own sorting method.
A ListView is always sorted by ListItem ('record')
The sorting uses the columnheaders indices.
Sorting without columnheaders isn't possible

Sorting is dependent of
- ListView.Sorted property: value 0 (not sorted) or 1 (sorted).
- ListView.SortKey the sorting column by it's index number.
The fist column in the ListView has index number 0.
- ListView.SortOrder sorting ascendingly: 0 or descendingly: 1.


All 'fields' in a ListView are string values.
Sorting is based on the property .Text of ListItems and ListSubItems.
So sorting is always alphanumeric.
For sorting of numbers and dates: see 9.1.

ListView sorting by ListItem
With LV_00
For jj = 1 To 4
.ColumnHeaders.Add , "Key " & jj, "Text " & jj, 80
Next

' . . . add data

.Sorted = 1
.SortKey = 0
.SortOrder = 0
.SortOrder = lvwascending
.SortOrder = 1
.Sortorder = lvwdescending
End With
ListView sorting by ListSubItem
With LV_00
For jj = 1 To 4
.ColumnHeaders.Add , "Key " & jj, "Text " & jj, 80
Next

' . . . add new data

.Sorted = 1
.SortKey = 3
.SortOrder = 1
End With

9.1 Sorting by date

Several methods to sort dates are available.
If you use the ISO-format yyyy/mm/dd (the 'small-endian' format) you can sort every date correctly.
The sorting of a string is from left to right.

In VBA dates are numbers.
The number 1 is equal to 01-01-1900.
You can only perform an alphanumeric sort on numbers if all numbers have the same amount of digits.
An alphanumeric sort on numbers results in 61 greater than 567.
All dates between 18-05-1927 (10000) and 13-10-2137 (99999) contain 5 digits; alphanumerical sorting results in an identical order to numerical sorting.

If you want to show dates in the reportview in the format dd-mm-yyyy, you need an extra column for the sorting.
That column can be made invisible in the reportview.
So the sorting will take place in the hidden sorting column, the dates will be shown in the 'normal' column.

Sort by format

The ListView has 3 visible columns and 2 invisible columns.
The first column contains the ListItem with a string.
The second column is invisible and contains the ISO-formatted (yyyymmdd) date in column 4: a sorting column.
The third column is invisible: a sorting column for numbers.
The fourth visible column consists of normally formatted dates: dd-mm-yyyy.
The fifth column has some text e.g. the name of a department.

Data are being
- sorted, since .Sorted = 1
- sorted ascendingly, since .SortOrder = 0
- sorted by column 2, since .SortKey = 2
Private Sub UserForm_Initialize()
sn = Split("name datesort numbersort date department")

With LV_00
for jj = 0 to 4
.ColumnHeaders.Add , sn(jj), sn(jj), iif(jj<2, 0, 60)
Next

For j = 0 To 4
With .ListItems.Add(, , "Name " & j)
For jj = 1 To 3
.ListSubItems.Add , , Choose(jj, Format(Date - j, "yyyymmdd"),"",Date - j, sn(jj) & " " & j,)
Next
End With
Next

.Sorted = 1
.SortKey = 2
.SortOrder = 0
.View = 3
End With
End Sub
Sort by number

The ListView has 3 visible columns and 2 invisible columns.
The first column contains the ListItem with a string.
The second column is invisible and contains the number-formatted (CLng(Date –j)) date in column 4: a sorting column.
The third column is invisible and empty: a sorting column for numbers.
The fourth visible column consists of normally formatted dates: dd-mm-yyyy.
The fifth column has some text e.g. the name of a department.

Data are being
- sorted, since .Sorted = 1
- sorted ascendingly, since .SortOrder = 0
- sorted by column 2, since .SortKey = 2
Private Sub UserForm_Initialize()
sn = Split("name sortdate sortnumber date department")

With LV_00
For jj = 0 To 4
.ColumnHeaders.Add , sn(jj), sn(jj), iif(jj<2, 0, 60)
Next

For j = 0 To 4
With .ListItems.Add(, , "Name " & j)
For jj = 1 To 4
.ListSubItems.Add , , Choose(jj, CLng(Date - j),"",Date - j, sn(jj) & " " & j)
Next
End With
Next

.Sorted = 1
.SortKey = 2
.SortOrder = 0
.View = 3
End With
End Sub

9.2 Sort by number

The ListView's sorting method is alphanumerical.
It considers 61 to be greater than 287.
Only same sized (amount of digits) numbers will be identically sorted by numerical and alphanumerical sorting methods.
We can use 'format' to make numbers identical in amount of digits.
Format(61,"000") creates "061" and format(287,"000") "287".
Now alphanumerical sorting considers "287" to be greater than "061".

If you want to show numbers as number in the reportview, you will need an extra alphanumerical sorting 'column'.
You might prefer to hide this sorting column by making it invisible in de reportview.
The sorting column will be sorted, the number column will be shown.

Sort by format

The ListView has 3 visible columns and 2 invisible columns.
The first column contains the ListItem with a string.
The second column is invisible and empty.
The third column is invisible and contains the number in column5 in the format("000000").
The fourth visible column consists of normally formatted dates: dd-mm-yyyy.
The fifth column is the number of a department.

Data are being
- sorted, since .Sorted = 1
- sorted descendingly, since .SortOrder = 1
- sorted by column 2, since .SortKey = 3
Private Sub UserForm_Initialize()
sn = Split("name sortdate sortnumber date department number")

With LV_00
For jj = 0 To 5
.ColumnHeaders.Add , sn(jj), sn(jj), iif(jj<2,0,60)
Next

For j = 0 To 4
With .ListItems.Add(, , "Name " & j)
For jj = 1 To 4
.ListSubItems.Add , , Choose(jj, "", Format(12^j, string(15,"0")),format(Date +j,"dd-mm-yyyy"), 12^j)
Next
End With
Next

.Sorted = 1
.SortKey = 2
.SortOrder = 1
.View = 3
End With
End Sub

9.3 Sort interactively

We all are familiar with the option in an Explorer window to switch the sorting order by clicking the columnheaders.
The ListView provides this too.

The ListView has a _ColumnClick event
Private Sub ListView_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)
The ColumnHeader argument of the ListView_ColumnClick event is the columnheader ( and column button at the same time) that the user has clicked.
Every columnheader has the property .SubItemIndex.
This property .SubItemIndex represents the link between the columnheader and a ListSubItem.
If you want to use the columnheader button to toggle between sorting ascendingly/descendingly:
Private Sub LV_00_ColumnClick(ByVal ColomnHeader As MSComctlLib.ColumnHeader)
With LV_00
.SortKey = ColomnHeader.SubItemIndex
.SortOrder = Abs(.SortOrder = 0)
End With
End Sub

9.4 Interactive sorting by date

For the sort by date we need a sorting column.
Let's use the first ListView column as date sorting column.
Let's use the second ListViewcolumn as number sorting column.
If no date or number sorting is necessary we keep those as empty columns.
That's no imepdiment to the properly functioning of the ListView.
If several date columns or number column have to be sorted you need a sorting column for each of those.

In the eventprocedure ListView_ColumnClick the property .Sortkey gets the value of the column that has to be sorted.
That value is the property .SubItemIndex corresponding to each columnheader.
For sorting by date the clicked column isn't the sorting column.
We can use the columnheader's property .Tag to link the column to the sorting column.

The dates will be part of a ListSubItem.
On top of that that they will have to be stored in a sortable format in the ListSubItem of the corresponding sorting column.
For dates the format can be either format(date,"yyyymmdd") or Clng(date).
Private Sub UserForm_Initialize()
sn = Sheet1.Cells(1).CurrentRegion

With LV_00
For jj = 1 To UBound(sn, 2)
.ColumnHeaders.Add(, sn(1, jj), sn(1, jj), Choose(jj, 60, 60, 120, 60)).Tag = IIf(IsDate(sn(2, jj)), 1, IIf(IsNumeric(sn(2, jj)), 2, ""))
If jj = 1 Then
.ColumnHeaders.Add , "date_", , 0
.ColumnHeaders.Add , "number_", , 0
End If
Next

.HideColumnHeaders = False

For j = 2 To UBound(sn)
With .ListItems.Add(, sn(j, 1), sn(j, 1))
.ListSubItems.Add , LV_00.ColumnHeaders(2).Key .ListSubItems.Add , LV_00.ColumnHeaders(3).Key
For jj = 2 To UBound(sn, 2)
.ListSubItems.Add , sn(1, jj), sn(j, jj), , "tooltiptexst" & jj
If IsDate(sn(j, jj)) Then .ListSubItems(1).Text = CLng(sn(j, jj))
If IsNumeric(sn(j, jj)) Then .ListSubItems(2).Text = Format(sn(j, jj), String(15, "0"))
Next
End With
Next

.Sorted = 1
.View = 3
end with
End Sub
The Eventprocedure
Private Sub LV_00_ColumnClick(ByVal ColomnHeader As MSComctlLib.ColumnHeader)
With LV_00
.SortKey = IIf(ColomnHeader.Tag = "", ColomnHeader.SubItemIndex, ColomnHeader.Tag)
.SortOrder = Abs(.SortOrder = 0)
End With
End Sub

9.5 Interactive sorting by number

For the sorting by number we need an alphanumerical sorting column.
Let's use the first ListView column as date sorting column.
Let's use the second ListView column as number sorting column.
If no date or number sorting is necessary we keep these columns empty.
That's no impediment to the properly functioning of the ListView.
If several date columns or number columns have to be sorted you need a sorting column for each of those.

In the eventprocedure ListView_ColumnClick the property .Sortkey gets the value of the column that has to be sorted.
That value is the property .SubItemIndex corresponding to each columnheader.
But to sort numbers the clicked column isn't the sorting column.
We can use the columnheader's property .Tag to link the column to the sorting column.

The numbers will be part of a ListSubItem.
On top of that they will have to be stored in a sortable alphanumerical format in the ListSubItem of the corresponding sorting column.
For number values the format can be format(number,string(15,"0")).
Private Sub UserForm_Initialize()
sn = Sheet1.Cells(1).CurrentRegion

With LV_00
For jj = 1 To UBound(sn, 2)
.ColumnHeaders.Add(, sn(1, jj), sn(1, jj), Choose(jj, 60, 60, 120, 60)).Tag = IIf(IsDate(sn(2, jj)), 1, IIf(IsNumeric(sn(2, jj)), 2, ""))
If jj = 1 Then
.ColumnHeaders.Add , "date_", , 0
.ColumnHeaders.Add , "number_", , 0
End If
Next

.HideColumnHeaders = False

For j = 2 To UBound(sn)
With .ListItems.Add(, sn(j, 1), sn(j, 1))
.ListSubItems.Add , LV_00.ColumnHeaders(2).Key .ListSubItems.Add , LV_00.ColumnHeaders(3).Key
For jj = 2 To UBound(sn, 2)
.ListSubItems.Add , sn(1, jj), sn(j, jj)
If IsDate(sn(j, jj)) Then .ListSubItems(1).Text = CLng(sn(j, jj))
If IsNumeric(sn(j, jj)) Then .ListSubItems(2).Text = Format(sn(j, jj), String(15, "0"))
Next
End With
Next

.Sorted = 1
.View = 3
end with
End Sub
Private Sub LV_00_ColumnClick(ByVal ColomnHeader As MSComctlLib.ColumnHeader)
With LV_00
.SortKey = IIf(ColomnHeader.Tag = "", ColomnHeader.SubItemIndex, ColomnHeader.Tag)
.SortOrder = Abs(.SortOrder = 0)
End With
End Sub

10 ListView save data

A user can make changes in elements of a ListView.
It is also possible to use VBA to adapt a ListView.
To save the result in a Listview you will have to read them in an array or string.
You can save the array in an Excel worksheet.
The string can be saved in Word, or as .txt or .csv file.

Array & Excel
With LV_00
ReDim sp(.ListItems.Count, .ColumnHeaders.Count)

For jj = 0 To .ColumnHeaders.Count - 1
sp(0, jj) = .ColumnHeaders(jj + 1).Text
Next

For j = 1 To UBound(sp)
sp(j, 0) = .ListItems(j)
For jj = 0 To UBound(sp, 2) - 1
sp(j, jj + 1) = .ListItems(j).ListSubItems(jj + 1)
Next
Next
End With

Sheet2.Cells(1).Resize(UBound(sp), UBound(sp, 2) + 1) = sp
String & .csv-file

If the first 2 ListSubItem-columns are sorting columns:
With LV_00
For jj = 1 To .ColumnHeaders.Count
If .ColumnHeaders(jj).Text <> "" Then c00 = c00 & "," & .ColumnHeaders(jj).Text
Next

For j = 1 To .ListItems.Count
c00 = c00 & vbCrLf & .ListItems(j)
For jj = 3 To .ListItems(j).ListSubItems.Count
c00 = c00 & "," & .ListItems(j).ListSubItems(jj)
Next
Next
End With

MsgBox Mid(c00, 2)
CreateObject("scripting.filesytemobject").createtextfile("G:\OF\ListView.csv").write c00