Type Mismatch Help
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 |
Type Mismatch Help
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 |
Type Mismatch Help
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 |
Type Mismatch Help
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 |
Type Mismatch Help
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 |
All times are GMT +1. The time now is 11:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com