Private racecardSheetName As String
Private dataSheetName As String

Private Sub TreeView1_BeforeLabelEdit(Cancel As Integer)
    ' Check if the node is a child node
    If Node.Parent <> "" Then
        ' Cancel the label edit
        Cancel = True
    End If
End Sub

Private Sub TreeView1_NodeClick(ByVal Node As MSComctlLib.Node)

    ' Declare variables
    Dim raceCourse As String
    Dim raceTime As String
    Dim targetSheet As Worksheet
    Dim targetRow As Long
    Dim offTime As Date
    Dim course As String
    Dim raceName As String
    Dim raceDate As Date
    Dim prizeMoney As String
    Dim formattedPrizeMoney As String
    Dim regex As Object
    Dim raceDist As String
    Dim raceClass As String
    Dim raceType As String
    Dim going As String
    Dim fieldSize As String

    ' Check if clicked node is a child node
    If Node.Parent <> "" Then

        ' Get race course and race time from clicked nodes
        raceCourse = Node.Parent.Text
        raceTime = Node.Text

        ' Set target sheet
        Set targetSheet = Worksheets(racecardSheetName)

        ' Find match in column B
        On Error Resume Next
        targetRow = targetSheet.Evaluate("match(timevalue(""" & raceTime & """),b:b,0)")
        On Error GoTo 0

        If targetRow <> 0 Then

            ' Get race data from target row
            offTime = targetSheet.Cells(targetRow, 2).Value
            course = targetSheet.Cells(targetRow, 3).Value
            raceName = targetSheet.Cells(targetRow, 4).Value
            raceDate = DateValue(targetSheet.Cells(targetRow, 1).Value)
            prizeMoney = targetSheet.Cells(targetRow, 10).Value
            raceDist = targetSheet.Cells(targetRow, 5).Value
            raceClass = targetSheet.Cells(targetRow, 6).Value
            raceType = targetSheet.Cells(targetRow, 7).Value
            going = targetSheet.Cells(targetRow, 9).Value
            fieldSize = targetSheet.Cells(targetRow, 8).Value

            ' Create a regular expression object
            Set regex = CreateObject("VBScript.RegExp")
            With regex
                .Pattern = "\D" ' Match any non-digit character
                .Global = True ' Match all occurrences
            End With

            ' Remove non-numeric characters from prizeMoney
            prizeMoney = regex.Replace(prizeMoney, "")
            formattedPrizeMoney = "£" & Format(Val(prizeMoney), "#,##0")

            ' Display race data in labels
            TimeLbl.Caption = Format(offTime, "h:mm")
            CourseLbl.Caption = course
            RaceNameLbl.Caption = raceName
            DateLbl.Caption = Format(raceDate, "dd mmm yy")
            prizeLbl.Caption = formattedPrizeMoney
            distLbl.Caption = raceDist & "f"
            classLbl.Caption = raceClass
            raceTypeLbl = raceType
            goingLbl = going
            runnersLbl = fieldSize


            ' Populate parent ListView with data from Sheet1
            PopulateParentListView raceCourse, raceTime

            ' Populate child ListView with data from Sheet2
            'PopulateChildListView raceTime



        Else
            ' Display error message if no match was found
            MsgBox "No race found for " & raceTime & " at " & raceCourse
        End If
    End If

End Sub
Private Sub PopulateParentListView(ByVal raceCourseName As String, ByVal raceTime As String)
    ' Clear existing items from parent ListView
    ListView1.ListItems.Clear

    ' Set the target sheet
    Dim targetSheet As Worksheet
    Set targetSheet = Worksheets(racecardSheetName)

    ' Populate parent ListView with data from Sheet1 based on selected race time
    Dim lastRow As Long
    lastRow = targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row

    Dim i As Long
    For i = 2 To lastRow
        If StrComp(targetSheet.Cells(i, 3).Value, raceCourseName, vbTextCompare) = 0 _
        And StrComp(targetSheet.Cells(i, 2).Value, raceTime, vbTextCompare) = 0 Then
        ' Add horse names to the parent ListView
        Dim listItem As MSComctlLib.listItem
        Set listItem = Me.ListView1.ListItems.Add(, , targetSheet.Cells(i, 12).Value)
        ' Add more subitems as needed
    End If

    Next i
End Sub


Private Sub UserForm_Initialize()
    ' Import racecard CSV data into a new sheet
    Dim newSheet As Worksheet
    Set newSheet = Workbooks("RaceCardAnalyser.xlsm").Worksheets.Add
    With newSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\Contango\Desktop\Racecard Analysis Project\Racecards\racecards.csv", Destination:=newSheet.Range("A1"))
        .TextFileCommaDelimiter = True 'Set delimiter to comma
        .TextFileParseType = xlDelimited
        .Refresh
    End With

    ' Import historical data CSV data into a new sheet
    'Dim newSheet2 As Worksheet
    'Set newSheet2 = Workbooks("RaceCardAnalyser.xlsm").Worksheets.Add
    'With newSheet2.QueryTables.Add(Connection:= _
        '"TEXT;C:\Users\Contango\Desktop\Racecard Analysis Project\Data\data.csv", Destination:=newSheet2.Range("A1"))
        '.TextFileCommaDelimiter = True 'Set delimiter to comma
        '.TextFileParseType = xlDelimited
        '.Refresh
    'End With

    ' Store the name of the new racecard sheet
    racecardSheetName = newSheet.Name

    ' Store the name of the new data sheet
    'dataSheetName = newSheet2.Name

    ' Create dictionary to store racecourses and times
    Dim raceData As Object
    Set raceData = CreateObject("Scripting.Dictionary")

    ' Loop through all rows of data
    Dim currRow As Long
    For currRow = 2 To newSheet.Cells(newSheet.Rows.Count, "A").End(xlUp).Row
        ' Get current race course and race time
        Dim raceCourse As String
        Dim raceTime As String
        raceCourse = newSheet.Cells(currRow, 3).Value
        raceTime = Format(newSheet.Cells(currRow, 2).Value, "h:mm")

        ' Add race course and race time to dictionary
        If Not raceData.Exists(raceCourse) Then
            raceData.Add raceCourse, New Collection
        End If

        ' Add race time to collection if it doesn't already exist
        Dim raceTimeCheck As Collection
        Set raceTimeCheck = raceData(raceCourse)
        Dim found As Boolean
        found = False
        Dim i As Long
        For i = 1 To raceTimeCheck.Count
            If raceTimeCheck(i) = raceTime Then
                found = True
                Exit For
            ElseIf raceTimeCheck(i) > raceTime Then
                raceTimeCheck.Add raceTime, Before:=i
                found = True
                Exit For
            End If
        Next i
        If Not found Then
            raceTimeCheck.Add raceTime
        End If
    Next currRow

    ' Populate TreeView with data from dictionary
    Dim raceCourses As Variant
    For Each raceCourses In raceData
        ' Add parent node for race course
        Dim currNode As Node
        Set currNode = TreeView1.Nodes.Add(, , raceCourses, raceCourses)
        currNode.Tag = raceCourses

        ' Add child nodes for race times
        Dim raceTimes As Variant
        For Each raceTimes In raceData(raceCourses)
            Set currNode = TreeView1.Nodes.Add(raceCourses, tvwChild, , raceTimes)
            currNode.Tag = raceTimes
        Next raceTimes
    Next raceCourses

    ' Clean up
    Set newSheet = Nothing
    Set raceData = Nothing

End Sub

Private Sub UserForm_Terminate()

    ' NOTES: Remove the sheet created when the form was initialized
    Application.DisplayAlerts = False 'Suppress alert message
    Workbooks("RaceCardAnalyser.xlsm").Sheets(racecardSheetName).Delete

    'Workbooks("RaceCardAnalyser.xlsm").Sheets(dataSheetName).Delete
    Application.DisplayAlerts = True

End Sub