I have a userform with textbox1 and textbox2. The input into each is time as
hh:mm. I need to subtract the two. However I get a type mismatch for the
following formula:

ttltime2 = textbox2 - textbox1

ttltime2 is DIM as Double.

How do I correct this?

Another question I have is this: Is there a way to format the above
textboxes to
hh:mm so that I can enter 0823 and have it accepted as 08:23?

Thanks,
Les

I'd check the value to make sure that they are both numeric:

if isnumeric(me.textbox1.value) _
and isnumeric(me.textbox2.value) then
me.ttltime2.value = me.textbox2.value - me.textbox1.value
else
me.ttltime2.value = "Non-numerics in textbox1 or textbox2"
end if



WLMPilot wrote:

I have a userform with textbox1 and textbox2. The input into each is time as
hh:mm. I need to subtract the two. However I get a type mismatch for the
following formula:

ttltime2 = textbox2 - textbox1

ttltime2 is DIM as Double.

How do I correct this?

Another question I have is this: Is there a way to format the above
textboxes to
hh:mm so that I can enter 0823 and have it accepted as 08:23?

Thanks,
Les

--

Dave Peterson

You could do something like:

Option Explicit
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

If InStr(1, Me.TextBox1.Value, ":", vbTextCompare) = 0 Then
'do some work
If IsNumeric(Me.TextBox1.Value) Then
Me.TextBox1.Value = Format(Me.TextBox1.Value, "00\:00")
Else
Beep
Me.TextBox1.Value = ""
Cancel = True
End If
End If

End Sub

But you may want to add a few more validity checks--so that 9876 doesn't end up
as 98:76.


WLMPilot wrote:

I have a userform with textbox1 and textbox2. The input into each is time as
hh:mm. I need to subtract the two. However I get a type mismatch for the
following formula:

ttltime2 = textbox2 - textbox1

ttltime2 is DIM as Double.

How do I correct this?

Another question I have is this: Is there a way to format the above
textboxes to
hh:mm so that I can enter 0823 and have it accepted as 08:23?

Thanks,
Les

--

Dave Peterson

ps. I didn't notice that ttltime2 was a double.

if isnumeric(me.textbox1.value) _
and isnumeric(me.textbox2.value) then
ttltime2 = me.textbox2.value - me.textbox1.value
else
ttltime2 = 999999 'or 0???
end if

Dave Peterson wrote:

I'd check the value to make sure that they are both numeric:

if isnumeric(me.textbox1.value) _
and isnumeric(me.textbox2.value) then
me.ttltime2.value = me.textbox2.value - me.textbox1.value
else
me.ttltime2.value = "Non-numerics in textbox1 or textbox2"
end if

WLMPilot wrote:

I have a userform with textbox1 and textbox2. The input into each is time as
hh:mm. I need to subtract the two. However I get a type mismatch for the
following formula:

ttltime2 = textbox2 - textbox1

ttltime2 is DIM as Double.

How do I correct this?

Another question I have is this: Is there a way to format the above
textboxes to
hh:mm so that I can enter 0823 and have it accepted as 08:23?

Thanks,
Les

--

Dave Peterson

--

Dave Peterson

It would be easier to Dim as Date and then input the times in a Date/Time
format such as 00:00am/pm or use 24 Hour times 08:00, 14:30. You can then
subtract one from the other to get ttltime worked. Then you would Dim
ttltime as Double to get the answer in decimal hours. ttltime = Hours(CkOut)
+ (Minutes(CkOut)/60) - Hours(CkIn) + (Minutes(CkIn)/60).

Mike F
"Dave Peterson" wrote in message
...
You could do something like:

Option Explicit
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

If InStr(1, Me.TextBox1.Value, ":", vbTextCompare) = 0 Then
'do some work
If IsNumeric(Me.TextBox1.Value) Then
Me.TextBox1.Value = Format(Me.TextBox1.Value, "00\:00")
Else
Beep
Me.TextBox1.Value = ""
Cancel = True
End If
End If

End Sub

But you may want to add a few more validity checks--so that 9876 doesn't
end up
as 98:76.


WLMPilot wrote:

I have a userform with textbox1 and textbox2. The input into each is
time as
hh:mm. I need to subtract the two. However I get a type mismatch for
the
following formula:

ttltime2 = textbox2 - textbox1

ttltime2 is DIM as Double.

How do I correct this?

Another question I have is this: Is there a way to format the above
textboxes to
hh:mm so that I can enter 0823 and have it accepted as 08:23?

Thanks,
Les

--

Dave Peterson