What’s the correct format for a calculated time value on a userform?

I have a userform that takes my workday start time, end time, and two break times, then feeds them to cells on the spreadsheet. I can use those cells to calculate the total hours of my workday, but getting the userform to provide that calculation escapes me. I want to click a command button on the userform and have it calculate (or recalculate) the total hours.

After diligent online searching and cribbing from others, I got as far as the Sub below:

Private Sub cmdHours_Click()

Dim TotalHours

TotalHours = #9:00:00 AM#


    With Me
        .txtHours.Value = Format(TotalHours(.txtStop.Value) - (.txtStart.Value) _
        - ((.txtBack1.Value) - (.txtBreak1.Value)) - ((.txtBack2.Value) - (.txtBreak2.Value)), "hh:nn")
    End With
    
End Sub

This gives me a "Type Mismatch" error, which I'm sure is either the hh:nn at the end of the value format, or the TotalHours between the hashes at the top. What am I missing?

Solution

In VBA, you need to convert text input into time values before doing math on them, and #9:00:00 AM# isn't necessary in your case unless you're hardcoding a specific time.

Private Sub cmdHours_Click()

    Dim StartTime As Date
    Dim EndTime As Date
    Dim Break1Start As Date
    Dim Break1End As Date
    Dim Break2Start As Date
    Dim Break2End As Date
    Dim TotalHours As Double
    Dim BreakDuration As Double

    ' Convert text inputs to actual Date/Time values
    StartTime = TimeValue(Me.txtStart.Value)
    EndTime = TimeValue(Me.txtStop.Value)
    Break1Start = TimeValue(Me.txtBreak1.Value)
    Break1End = TimeValue(Me.txtBack1.Value)
    Break2Start = TimeValue(Me.txtBreak2.Value)
    Break2End = TimeValue(Me.txtBack2.Value)

    ' Calculate total time worked minus break durations
    BreakDuration = (Break1End - Break1Start) + (Break2End - Break2Start)
    TotalHours = (EndTime - StartTime) - BreakDuration

    ' Show result in textbox (convert to hours and minutes)
    Me.txtHours.Value = Format(TotalHours, "h:mm")

End Sub

Note: Make sure to enter times in a recognizable format (ie "2:00 PM", "14:00", etc...)