Is there a way to create a nested ListView in excel 2007?

[BrovashiftExcel]

Hi all,

Still working on my horse racing data project where I am pulling in data from 2 csv files and populating a listview (parent views) with data from Sheet1, and am creating a second listview in the code that is supposed to display data in nested childviews from Sheet2 when Parent View clicked. This works and returns all rows from Sheet2 where horseName = horseName, I can see this in the Locals window when stepping through the code.

I have 2 problems; one is that because I am creating the second (child) listview programatically, and is not a physical control object added to the userform, I dont have access to properties in the property window, and trying to use ChildListView. 'Top', 'Left', 'Width', 'Height' are not recognised. This means that it just creates a very small gray box in the upper most top left corner. Useless!
And the second thing is this is not what I wanted really. Ideally I'd like to have created/used one listview control that has the functionality for a hierarchical structure, parent-child views, but not sure if this is possible in excel 2007.

Is there a better way I can achieve this? If the only option is to created the 'appearence' of nested listviews, then is there another way I can use Top, Left, Width, Height in the code? (commented out here)

Private Sub PopulateChildListView(horseName As String)
    
    ' Create the ChildListView dynamically
    Dim ChildListView As ListView
    Set ChildListView = Me.Controls.Add("MSComctlLib.ListViewCtrl.2", "ChildListView", True)
    
    ' Set the properties of the ChildListView
    With ChildListView
        ' Set the position and size of the ChildListView
        '.Top = Me.ListView1.Top + ListView1.Height + 10 ' Adjust the top position as needed
        '.left = Me.ListView1.left
        '.Width = Me.ListView1.Width
        '.Height = 200 ' Adjust the height as needed
        
        ' Set the appearance properties
        .BackColor = RGB(235, 235, 235) ' Light gray color
        .HideColumnHeaders = False
        .View = lvwReport
        .Gridlines = True
        
        ' Clear existing items from the ChildListView
        .ListItems.Clear
    End With
    
    ' Set the target sheet (Sheet2)
    Dim targetSheet As Worksheet
    Set targetSheet = Worksheets(dataSheetName) ' Replace "Sheet2" with the actual name of the sheet
    
    ' Find the last row in Sheet2
    Dim lastRow As Long
    lastRow = targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row
    
    ' Flag variable to indicate if any matching data is found
    Dim matchFound As Boolean
    matchFound = False
    
    ' Loop through the rows in Sheet2 and populate the ChildListView with matching data
    Dim i As Long
    For i = 2 To lastRow ' Assuming the data starts from row 2
        Dim horseNameSheet2 As String
        horseNameSheet2 = targetSheet.Cells(i, 22).Value ' Assuming the horse name is in the first column of Sheet2
        
        ' Remove the region letters from the horse name in Sheet2
        Dim regionPos As Integer
        regionPos = InStr(horseNameSheet2, "(")
        If regionPos > 0 Then
            horseNameSheet2 = Trim(left(horseNameSheet2, regionPos - 1))
        End If
        
        ' Compare the horse names
        If StrComp(horseNameSheet2, horseName, vbTextCompare) = 0 Then
            Dim listItem As MSComctlLib.listItem
            Set listItem = ChildListView.ListItems.Add(, , targetSheet.Cells(i, 5).Value) ' Assuming the data you want to display is in the second column of Sheet2
            ' Add additional subitems if needed
            
            matchFound = True ' Set the flag to indicate a match was found
        End If
    Next i
    
    ' Check if any matching data was found
    If Not matchFound Then
        MsgBox "No matching data found for horse name: " & horseName, vbInformation
        ' Remove the ChildListView control
        Me.Controls.Remove ChildListView
    End If
    
End Sub

Or.... is that functionality available in later versions of excel vba? A later version that doesn't require subscription like 365.

TIA