Userform addition question

[Patrick Simonds]

I have a user form in which I enter a start time (Textbox1) and an end time
in (Textbox2).

What I need to have happen on the Userform is for Textbox3 to be filled in
with the total time worked. The start and end times are entered as times
(hh:mm) but the result in Textbox3 must be in decimal format (8.3).

 

[Bob Phillips]

Hi Patrick,

TextBox3.Text = Format((CDate(TextBox2.Text) - _
CDate(TextBox1.Text)) * 24, _
"##0.0")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Patrick Simonds]

Just some amplification. The code below is what I was using when we entered
time in the decimal format, but I have not been successful in converting it
to what is required now.



Sub TotalHours2()

Dim bTest1 As Boolean
Dim bTest2 As Boolean
Dim bTest3 As Boolean
Dim dblElapsed

With UserForm1

If IsNumeric(.TextBox4.Text) And _
IsNumeric(.TextBox5.Text) And _
IsNumeric(.ComboBox12.Text) Then

dblElapsed = (CDbl(.TextBox5.Text) - CDbl(.TextBox4.Text) -
CDbl(.ComboBox12.Text))

.TextBox6.Value = Format(dblElapsed, "#0.00")

If .ComboBox2.Value = "" Then GoTo EnterCode
GoTo EndMacro

EnterCode:

.ComboBox2.Value = "01"

Else

'MsgBox "There is an invalid time"

End If

GoTo EndMacro

ClearBox:

UserForm1.TextBox4.Value = Format("", "")
UserForm1.TextBox5.Value = Format("", "")
UserForm1.TextBox6.Value = Format("", "")

EndMacro:

End With

End Sub

 

[Tom Ogilvy]

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If InStr(TextBox2.Text, ":") And _
Trim(TextBox2.Text) <> "" Then
If InStr(TextBox1.Text, ":") And _
Trim(TextBox1.Text) <> "" Then
TextBox3.Text = Format((CDate(TextBox2.Text) _
- CDate(TextBox1.Text)) * 24, "#.0")
End If
End If
End Sub
Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If InStr(TextBox1.Text, ":") And _
Trim(TextBox1.Text) <> "" Then
If InStr(TextBox2.Text, ":") And _
Trim(TextBox2.Text) <> "" Then
TextBox3.Text = Format((CDate(TextBox2.Text) _
- CDate(TextBox1.Text)) * 24, "#.0")
End If
End If
End Sub


You can add other checks to make sure the end time is greater than the start
time or to calculate over midnight.