Create ListView in VBA at Runtime

Compatibility Issues working with Application in Older Version Of Office

After the recent upgrades in Microsoft products, we can notice few compatibility issues in components like ListView, TreeView, ImageList etc., when upgrading VBA applications from Office 2003 to New versions. In such scenarios, if you have the corresponding OCX file registered in your machine, It is better to create these controls during run-time.

Sometimes, if we manually add these controls, save it and then open it in another machine, these controls will not appear or work properly (especially ListView). Not sure, whether Microsoft will provide ay fix for this. Sometimes, these controls created in latest version office will not work in older version.

Create Controls At Execution

Creating commands at run-time would solve in some cases. Lets see how to create a ListView when the VBA code is getting executed. Create a New Excel workbook, Pess Alt + F11 to view VB Editor. Copy paste the below code.

Private Sub Create_ListView_Dynamic()
    'Declare Variable Names
    Dim oLv As ListView
    Dim Wsheet As Worksheet
    
    'Create ListView in WorkSheet
    Set Wsheet = ThisWorkbook.Sheets(1)
    Set oLv = Wsheet.OLEObjects.Add(ClassType:="MSComctlLib.ListViewCtrl.2", _
        Link:=False, DisplayAsIcon:=False, Left:=100, Top:=100, Width:=300, Height:=100).Object
    
    'Give ListView Control a Name
    oLv.Name = "ListCust"
    
    'Assign Value to Other Properties
    With oLv
        .Left = 20
        .Top = 20
        .Height = 100
        .Width = 492
        .Visible = True
        .View = lvwReport
    End With
    
End Sub

Run the above code by Pressing F5 Command button. A Listview control will added to the First worksheet in your active Workbook.

Now that we have created the ListView, how do we add the ColumnHeaders and Insert Data

Private Sub Access_ListView_Add_Data()
    'Declare Variable Names
    Dim i As Integer
    Dim oLv As ListView
    Dim oLi As ListItem
    Dim Wsheet As Worksheet
    
    'Get ListView in WorkSheet to an Object
    Set Wsheet = ThisWorkbook.Sheets(1)
    Set oLv = Wsheet.OLEObjects("ListCust").Object
     
    'Clear Header & Add Column Headers
    oLv.ColumnHeaders.Clear
    With oLv
        .ColumnHeaders.Add 1, "Key1", "Key1"
        .ColumnHeaders.Add 2, , "Header2"
        .ColumnHeaders.Add 3, , "Header3"
        .ColumnHeaders.Add 4, , "Header4"
        .ColumnHeaders.Add 5, , "Header5"
        .ColumnHeaders.Add 6, , "Header6"
        .View = lvwReport
    End With
    
    'Add Data to ListView
    oLv.ListItems.Clear
    For i = 1 To 5
        Set oLi = oLv.ListItems.Add(i, , "KeyVal" & i)
        oLi.SubItems(1) = "H2Val" & i
        oLi.SubItems(2) = "H2Val" & i
        oLi.SubItems(3) = "H2Val" & i
        oLi.SubItems(4) = "H2Val" & i
        oLi.SubItems(5) = "H2Val" & i
    Next i
    
    'In Some Systems, ListView Value will not be Visible when you just run above code.
    'Execute below code to get the values visible & align properly
    oLv.Visible = False
    oLv.Visible = True
    ActiveSheet.Shapes("ListCust").Select
    With Selection
        .Placement = xlFreeFloating
        .PrintObject = True
    End With
    ActiveWindow.SmallScroll Down:=-24
    ActiveWindow.SmallScroll Up:=24
    
End Sub

Hope this is helpful when you want to create OLEobjects at runtime. Leave your comments if you like the solution.