.ListView control in Excel spreadsheets.md

ListView control in Excel spreadsheets

List view controls can be exceptionally useful for viewing data on a spreadsheet.

Step 1: Add control to spreadsheet.

Click on the Insert dropdown button in the Controls group of the Developer tab. When here click the More controls button of the ActiveX Controls sub menu.

Step 2: Find ListView control

Scroll down through the More Controls dialogue until you find Microsoft ListView Control, version 6.0.

Step 3: Draw the ListView control

Draw the list view control on the Excel spreadsheet.

Step 4: Change ListView properties

Right click on the ListView control. In the context menu click on the Properties button in the ListViewCtrl Object menu.

Note: You will only be able to access this menu in Design Mode which you will be able to access from the Controls group in the Developer tab.

When here we can start to change the list view properties. The list view properties we will likely want are:

General tab

View:               3-lvwReport
HideSelection:      false
MultiSelect:        true
AllowColumnReorder: true
FullRowSelect:      true
GridLines:          true

Column Headers tab

Here click Insert Column until you have the correct number of columns. For each column you can change the Text property (the text displayed in the column header) Key is the Key property in VBA. All other properties you can likely ignore.

Finally

Apply the changes and close the properties dialog. Give this listview a name! This will be used later to fill the listview with data!

Fill the listview with data

Now that the list view has been created, we can now fill it with data.

sub fillData(sh as Worksheet, ByVal sLVName as string)
  'Get object
  Dim lv as Object, lvi as 
  set lv = sh.OLEObjects(sLVName).Object
  
  'Clear existing data
  lv.ListItems.Clear
  
  'Add data
  Dim lvi as object
  
  'Row 1
  Set lvi = ListView.ListItems.Add()  
  lvi.Text = "Row_1_1"
  lvi.ListSubItems.Add().Text = "Row_1_2"
  lvi.ListSubItems.Add().Text = "Row_1_3"
  
  'Row 2
  Set lvi = ListView.ListItems.Add()  
  lvi.Text = "Row_2_1"
  lvi.ListSubItems.Add().Text = "Row_2_2"
  lvi.ListSubItems.Add().Text = "Row_2_3"
  
  'Row 3
  Set lvi = ListView.ListItems.Add()  
  lvi.Text = "Row_3_1"
  lvi.ListSubItems.Add().Text = "Row_3_2"
  lvi.ListSubItems.Add().Text = "Row_3_3"
  
  'Row 4
  Set lvi = ListView.ListItems.Add()  
  lvi.Text = "Row_4_1"
  lvi.ListSubItems.Add().Text = "Row_4_2"
  lvi.ListSubItems.Add().Text = "Row_4_3"
  
 'Row 5
  Set lvi = ListView.ListItems.Add()  
  lvi.Text = "Row_5_1"
  lvi.ListSubItems.Add().Text = "Row_5_2"
  lvi.ListSubItems.Add().Text = "Row_5_3"
end sub