calculating textboxes on userform with custom Formats

[Brenton Hombsch]

I have a userform where users can enter times for a team and i am wanting to add the four times together but not sure how to do it.

Each textbox is formatted "0\:00.\00" so that when the user enters the time eg 1500 it shows as 00:15.00 (minutes:seconds.00)

I don't even know where to start in vba mode with this!

Textboxes are named

"Time1"
"Time2"
"Time3"
"Time4"

I am wanting to put the total time of all 4 texboxes into "TotalTime" textbox after pressing a "Calculate" button

Please help

 

[Rosen]

Here is some code which should do the trick:

Private Sub Calculate_Click()
    
    Dim Minutes As Long
    Dim Seconds As Double
    Dim Point As Double
    Dim TotalSeconds As Double
    Dim Temp() As String
    Dim sTime As String
    ' ---------------------------------------------------------------------------------------------
    ' Convert first Time TextBox
    ' ---------------------------------------------------------------------------------------------
    sTime = Me.Time1.Text
    Minutes = 0
    Seconds = 0#
    If Not Me.Time1.Text = VBA.Constants.vbNullString Then
        Temp = VBA.Strings.Split(sTime, ":")
        If (UBound(Temp) - LBound(Temp) + 1) = 2 Then
            On Error Resume Next
            Minutes = VBA.Conversion.CLng(Temp(0))
            Seconds = VBA.Conversion.CDbl(Temp(1))
            On Error GoTo 0
            TotalSeconds = TotalSeconds + Minutes * 60 + Seconds
        End If
    End If
    
    ' ---------------------------------------------------------------------------------------------
    ' Convert second Time TextBox
    ' ---------------------------------------------------------------------------------------------
    sTime = Me.Time2.Text
    Minutes = 0
    Seconds = 0#
    If Not Me.Time2.Text = VBA.Constants.vbNullString Then
        Temp = VBA.Strings.Split(sTime, ":")
        If (UBound(Temp) - LBound(Temp) + 1) = 2 Then
            On Error Resume Next
            Minutes = VBA.Conversion.CLng(Temp(0))
            Seconds = VBA.Conversion.CDbl(Temp(1))
            On Error GoTo 0
            TotalSeconds = TotalSeconds + Minutes * 60 + Seconds
        End If
    End If
    
    ' ---------------------------------------------------------------------------------------------
    ' Convert third Time TextBox
    ' ---------------------------------------------------------------------------------------------
    sTime = Me.Time3.Text
    Minutes = 0
    Seconds = 0#
    If Not Me.Time3.Text = VBA.Constants.vbNullString Then
        Temp = VBA.Strings.Split(sTime, ":")
        If (UBound(Temp) - LBound(Temp) + 1) = 2 Then
            On Error Resume Next
            Minutes = VBA.Conversion.CLng(Temp(0))
            Seconds = VBA.Conversion.CDbl(Temp(1))
            On Error GoTo 0
            TotalSeconds = TotalSeconds + Minutes * 60 + Seconds
        End If
    End If
    
    ' ---------------------------------------------------------------------------------------------
    ' Convert fourth Time TextBox
    ' ---------------------------------------------------------------------------------------------
    sTime = Me.Time4.Text
    Minutes = 0
    Seconds = 0#
    If Not Me.Time4.Text = VBA.Constants.vbNullString Then
        Temp = VBA.Strings.Split(sTime, ":")
        If (UBound(Temp) - LBound(Temp) + 1) = 2 Then
            On Error Resume Next
            Minutes = VBA.Conversion.CLng(Temp(0))
            Seconds = VBA.Conversion.CDbl(Temp(1))
            On Error GoTo 0
            TotalSeconds = TotalSeconds + Minutes * 60 + Seconds
        End If
    End If
    
    ' ---------------------------------------------------------------------------------------------
    ' Convert it all into a displayable MM:SS.SS format
    ' ---------------------------------------------------------------------------------------------
    'MsgBox "Total Seconds = " & TotalSeconds
    Minutes = WorksheetFunction.RoundDown(TotalSeconds / 60, 0)
    Seconds = TotalSeconds Mod 60
    Point = TotalSeconds - ((Minutes * 60) + Seconds)
    Seconds = Seconds + Point
    Me.TotalTime.Text = VBA.Strings.Format(Minutes, "00") & ":" & VBA.Strings.Format(Seconds, "00.00")
End Sub

Please note that this is untested and any works should be saved before attempting to run it.

Hope this helps!

 

[Brenton Hombsch]

This is EXACTLY what i was after. Thankyou very much!!! Works perfectly