Focus
 

Hello,

I wonder if someone can help me with this: I'm trying to confirm a valid
time entry in a form field, and if invalid, clear the entry and put the
focus back in that field. If I use the following code it does work (not
the best method, no doubt), but if I remove the UserForm1.Hide and
UserForm1.Show lines, the focus always moves to the next field. I know I'm
missing something basic here, not sure what. Thanks,

Dave

If Private Sub txtTime1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo BadTime
Dim X
txtTime1.Text = Format(Val(txtTime1.Text), "00:00")
X = TimeValue(txtTime1.Text)
Exit Sub
BadTime:
UserForm1.Hide
MsgBox ("Invalid time")
txtTime1.Text = ""
txtTime1.SetFocus
UserForm1.Show
End Sub

Focus
 

"David Unger" wrote...
I wonder if someone can help me with this: I'm trying to confirm a valid
time entry in a form field, and if invalid, clear the entry and put the
focus back in that field. If I use the following code it does work (not
the best method, no doubt), but if I remove the UserForm1.Hide and
UserForm1.Show lines, the focus always moves to the next field. I know I'm
missing something basic here, not sure what. Thanks,
....
Private Sub txtTime1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo BadTime
Dim X
txtTime1.Text = Format(Val(txtTime1.Text), "00:00")
X = TimeValue(txtTime1.Text)
Exit Sub
BadTime:
UserForm1.Hide
MsgBox ("Invalid time")
txtTime1.Text = ""
txtTime1.SetFocus
UserForm1.Show
End Sub

Use the Cancel parameter to prevent exiting the field. Also, error trapping
is a crude way to handle type checking. Try something like

Private Sub Time_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If IsError(Evaluate("--""" & Time.Value & """")) Then
MsgBox "invalid time: " & Time.Value
Time.Value = ""
Cancel = True
End If
End Sub

Focus
 

Harlan,

Thanks for bringing my attention to the Cancel parameter, and the tip
on error handling. I knew there had to be a better way. Much
appreciated,

Dave

Focus
 

Harlan,

I've had a chance to try out your code, can't seem to get it to work.
No matter what I enter in the field, eg., 2500, it doesn't get trapped.
Also, I don't understand your Evaluate statement, the --"" and """".
Thanks, sorry for being a nuisance,

Dave

Focus
 

hi,
try this

BadTime:
MsgBox ("Invalid time")
txttime1.SetFocus
txttime1.Text = ""
Cancel = True
End Sub

bye, ste

Focus
 

"Dave Unger" wrote...
I've had a chance to try out your code, can't seem to get it to work.
No matter what I enter in the field, eg., 2500, it doesn't get trapped.
Also, I don't understand your Evaluate statement, the --"" and """".
Thanks, sorry for being a nuisance,

My fault. I only tried nonnumeric text in the Time field/textbox. If you
want to avoid error trapping, use

If IsError(Evaluate("TIMEVALUE(""" & Time.Value & """)")) Then

The argument to Evaluate is a call to the worksheet TIMEVALUE function,
which needs to look like a string constant, thus the doubled double quotes.

Focus
 

Hi David,

Here's something I use that might help.

It passes the textbox string to fcnCheckUserEnteredTime which returns an
empty string if a time isn't entered. At the moment it just returns the user
to the textbox1 and selects the text but you can easily change it to blank
the text if you like. (That's not a bad idea anyway because that way you
don't have to handle the user not being able to close the form if you are
trapping them in a textbox - there are ways around that.)


Note that fcnCheckUserEnteredTime allows users to enter times as HH:MM,
HHMM, or HH (or indeed MM) which I find useful because it's such a pain
typing in the colon all the time.

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim tmpTime As String

'Check the time is ok
tmpTime = fcnCheckUserEnteredTime(TextBox1.Text)
If tmpTime = "False" Then
Cancel = True
TextBox1.SelStart = 0
TextBox1.SelLength = Len(TextBox1.Text)
Exit Sub
Else
TextBox1.Text = tmpTime
End If
End Sub

Private Function fcnCheckUserEnteredTime(myStr As String) As String
'Converts text into a string of time as "hh:mm"
'Returns 11 as 11:00, 25 as 00:25, 1125 as 11:25

'If it is blank then leave as is - return empty string
If myStr = "" Then
Exit Function
'If user has already entered time correctly
ElseIf InStr(myStr, ":") 0 Then
If IsDate(myStr) Then
'Return it as is
fcnCheckUserEnteredTime = Format(CDate(myStr), "hh:mm")
Else
'There must be a problem with it
fcnCheckUserEnteredTime = "False"
End If

'Otherwise, contruct a time
ElseIf IsNumeric(myStr) Then

'Number of char determines how we handle it
Select Case Len(myStr)
Case 1
fcnCheckUserEnteredTime = _
Format((myStr & ":" & "00"), "hh:mm")
Case 2
If CInt(myStr) < 24 Then
fcnCheckUserEnteredTime = _
Format(CDate(myStr & ":" & 0), "hh:mm")
ElseIf CInt(myStr) < 61 Then
fcnCheckUserEnteredTime = _
Format(CDate(0 & ":" & myStr), "hh:mm")
Else
fcnCheckUserEnteredTime = "False"
End If
Case 3
'Last two char must be minutes
If CInt(Right$(myStr, 2)) < 60 Then _
fcnCheckUserEnteredTime = _
Format(CDate(Left$(myStr, 1) & ":" _
& Right$(myStr, 2)), "hh:mm") _
Else: fcnCheckUserEnteredTime = "False"

Case 4
'Last two char must be minutes, first two hours
If CInt(Left$(myStr, 2)) < 24 _
And CInt(Right$(myStr, 2)) < 60 Then _
fcnCheckUserEnteredTime = _
Format(CDate(Left$(myStr, 2) & ":" & Right$(myStr, 2)),
"hh:mm") _
Else: fcnCheckUserEnteredTime = "False"
Case Else
fcnCheckUserEnteredTime = "False"
End Select

'There must be some other problem
Else
fcnCheckUserEnteredTime = "False"
End If

End Function

"David Unger" wrote in message
...
Hello,

I wonder if someone can help me with this: I'm trying to confirm a valid
time entry in a form field, and if invalid, clear the entry and put the
focus back in that field. If I use the following code it does work (not
the best method, no doubt), but if I remove the UserForm1.Hide and
UserForm1.Show lines, the focus always moves to the next field. I know
I'm
missing something basic here, not sure what. Thanks,

Dave

If Private Sub txtTime1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo BadTime
Dim X
txtTime1.Text = Format(Val(txtTime1.Text), "00:00")
X = TimeValue(txtTime1.Text)
Exit Sub
BadTime:
UserForm1.Hide
MsgBox ("Invalid time")
txtTime1.Text = ""
txtTime1.SetFocus
UserForm1.Show
End Sub

Focus
 

Harlan, me again,

I just can't seem to get it to work properly - no matter what I throw
at it (e.g., 09:55 or 09:65), it always follows the error path. I've
tryed changing a few things, to no avail - any more suggestions?
Thanks

Dave

Focus
 

Harlan, me again,

I just can't seem to get it to work properly - no matter what I throw
at it (e.g., 09:55 or 09:65), it always follows the error path. I've
tryed changing a few things, to no avail - any more suggestions?
Thanks

Dave

Focus
 

Show the code you are currently using. Perhaps you have implemented it
incorrectly.

--
Regards,
Tom Ogilvy

"Dave Unger" wrote in message
ups.com...
Harlan, me again,

I just can't seem to get it to work properly - no matter what I throw
at it (e.g., 09:55 or 09:65), it always follows the error path. I've
tryed changing a few things, to no avail - any more suggestions?
Thanks

Dave

Focus
 

Hi Tom,

I'll have to do a 180 from what I just said - for some reason when I
did a copy/paste of Harlan's code, an extra character got thrown in.
Now that I've corrected that, ALL number combinations seem to pass. My
form field (txtTime) restricts the entry to numbers only, the 2nd line
in the code converts it to a time format. Thanks for having a look,

Dave

Private Sub txtTime_Exit(ByVal Cancel As MSForms.ReturnBoolean)
txtTime.Value = Format(txtTime.Value, "00:00")
If IsError(Evaluate("TIMEVALUE(""*" & txtTime.Value & """)")) Then
txtTime.Value = ""
Cancel = True
End If
End Sub

Focus
 

Harlan didn't use "-" and he didn't use the Format statement, either:

Option Explicit
Private Sub txtTime_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If IsError(Evaluate("TIMEVALUE(""" & txtTime.Value & """)")) Then
txtTime.Value = ""
Cancel = True
End If
End Sub



Dave Unger wrote:

Hi Tom,

I'll have to do a 180 from what I just said - for some reason when I
did a copy/paste of Harlan's code, an extra character got thrown in.
Now that I've corrected that, ALL number combinations seem to pass. My
form field (txtTime) restricts the entry to numbers only, the 2nd line
in the code converts it to a time format. Thanks for having a look,

Dave

Private Sub txtTime_Exit(ByVal Cancel As MSForms.ReturnBoolean)
txtTime.Value = Format(txtTime.Value, "00:00")
If IsError(Evaluate("TIMEVALUE(""*" & txtTime.Value & """)")) Then
txtTime.Value = ""
Cancel = True
End If
End Sub

--

Dave Peterson

Focus
 

Dave,

I only use the Format statement because I'm entering the time as 900,
not 09:00 - however, I've tried it without the Format statement,
doesn't work either.

It's interesting that you mention the "-", I don't see it in my
posting, but that's the extra character I was getting when I copied and
pasted Harlan's example.

thanks,

Dave

Focus
 

I didn't realize you were entering your times that way, but I added that format
statement back and it sure seemed to work ok for me (without that "-" stuff).

But if I enter 966, your format statement makes it look like: 9:66 and excel is
smart enough to change it to 10:06.

Is that bad?

If yes, then maybe you can incorporate Harlan's error checking and your
conversion into one procedu

Option Explicit
Private Sub txtTime_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim res As Variant
Dim myStr As String
myStr = Format(txtTime.Value, "00:00")
res = Evaluate("TIMEVALUE(""" & myStr & """)")
If IsError(res) Then
txtTime.Value = ""
Cancel = True
Else
txtTime.Value = Format(res, "hh:mm")
End If
End Sub



Dave Unger wrote:

Dave,

I only use the Format statement because I'm entering the time as 900,
not 09:00 - however, I've tried it without the Format statement,
doesn't work either.

It's interesting that you mention the "-", I don't see it in my
posting, but that's the extra character I was getting when I copied and
pasted Harlan's example.

thanks,

Dave

--

Dave Peterson

Focus
 

That appears to be a bug in the google beta. It appears to add a hyphen
sometimes when you paste code.

Private Sub txtTime_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim s as String
s = Trim(txtTime)
if instr(s,":") then
s = replace(s,":","")
End if
if len(s) <= 4 then
if isnumeric(s) then
txtTime.Value = format(clng(s),"00:00")
if Not IsError(Evaluate("TIMEVALUE(""" _
& txtTime.Value & """)")) then
Exit sub
End if
end if
End if
txtTime.Value = ""
Cancel = True
End Sub

there should be no hyphens (-) in this code although there is an underscore
"_"

--
Regards,
Tom Ogilvy




"Dave Unger" wrote in message
oups.com...
Dave,

I only use the Format statement because I'm entering the time as 900,
not 09:00 - however, I've tried it without the Format statement,
doesn't work either.

It's interesting that you mention the "-", I don't see it in my
posting, but that's the extra character I was getting when I copied and
pasted Harlan's example.

thanks,

Dave

Focus
 

Hello,

Dave and Tom, thanks for your code, but in both cases, it would let all
entries through, valid or invalid. I'm wondering, I'm using Excel 97, and
the REPLACE function in Tom's code, "s = Replace(s, ":", "")" wasn't
recognized, used "s = Left$(s, 2) & Right$(s, 2)" instead. Might it be
that Excel 97 handles the ISERROR function differently than later versions?

I won't bother you any more with this. For the time being I'll go back to
using the ONERROR GOTO statement, that seems to work for me. Tom, thanks
for the heads up about Google inserting the "-", that did throw me off at
first. On a side note, what's the reason for the CLng in Format(CLng(s),
"00:00")?

Harlan, Tom, Dave, thank you for your efforts, much appreciated, and it was
a learning experience for me.

Until later,

Thanks

Dave

"Tom Ogilvy" wrote in message
...
That appears to be a bug in the google beta. It appears to add a hyphen
sometimes when you paste code.

Private Sub txtTime_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim s as String
s = Trim(txtTime)
if instr(s,":") then
s = replace(s,":","")
End if
if len(s) <= 4 then
if isnumeric(s) then
txtTime.Value = format(clng(s),"00:00")
if Not IsError(Evaluate("TIMEVALUE(""" _
& txtTime.Value & """)")) then
Exit sub
End if
end if
End if
txtTime.Value = ""
Cancel = True
End Sub

there should be no hyphens (-) in this code although there is an
underscore
"_"

--
Regards,
Tom Ogilvy




"Dave Unger" wrote in message
oups.com...
Dave,

I only use the Format statement because I'm entering the time as 900,
not 09:00 - however, I've tried it without the Format statement,
doesn't work either.

It's interesting that you mention the "-", I don't see it in my
posting, but that's the extra character I was getting when I copied and
pasted Harlan's example.

thanks,

Dave

Focus
 

Thanks ste,

The Cancel=True was the key

Dave

Focus
 

Thanks Gareth, I'll certainly have a look at it.

Dave

Focus
 

Replace was added in xl2k.

This line:
s = replace(s,":","")
could be replaced with:
s = application.substitute(s,":","")
and it'll work in all versions.

And format() works on numbers. So Tom converted the string in the textbox to a
number before he applied the formatting.

David Unger wrote:

Hello,

Dave and Tom, thanks for your code, but in both cases, it would let all
entries through, valid or invalid. I'm wondering, I'm using Excel 97, and
the REPLACE function in Tom's code, "s = Replace(s, ":", "")" wasn't
recognized, used "s = Left$(s, 2) & Right$(s, 2)" instead. Might it be
that Excel 97 handles the ISERROR function differently than later versions?

I won't bother you any more with this. For the time being I'll go back to
using the ONERROR GOTO statement, that seems to work for me. Tom, thanks
for the heads up about Google inserting the "-", that did throw me off at
first. On a side note, what's the reason for the CLng in Format(CLng(s),
"00:00")?

Harlan, Tom, Dave, thank you for your efforts, much appreciated, and it was
a learning experience for me.

Until later,

Thanks

Dave

"Tom Ogilvy" wrote in message
...
That appears to be a bug in the google beta. It appears to add a hyphen
sometimes when you paste code.

Private Sub txtTime_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim s as String
s = Trim(txtTime)
if instr(s,":") then
s = replace(s,":","")
End if
if len(s) <= 4 then
if isnumeric(s) then
txtTime.Value = format(clng(s),"00:00")
if Not IsError(Evaluate("TIMEVALUE(""" _
& txtTime.Value & """)")) then
Exit sub
End if
end if
End if
txtTime.Value = ""
Cancel = True
End Sub

there should be no hyphens (-) in this code although there is an
underscore
"_"

--
Regards,
Tom Ogilvy




"Dave Unger" wrote in message
oups.com...
Dave,

I only use the Format statement because I'm entering the time as 900,
not 09:00 - however, I've tried it without the Format statement,
doesn't work either.

It's interesting that you mention the "-", I don't see it in my
posting, but that's the extra character I was getting when I copied and
pasted Harlan's example.

thanks,

Dave




--

Dave Peterson

Focus
 

Dave,

The reason I was asking - the Format line seems to work whether CLng is
present or not. As I'm still on the steep part of the learning curve,
I probably wouldn't have realized the need for it.

Dave

Focus
 

Tom

True. I am finding I have to be very careful when copying from postings.

The hyphen(s) are thrown in with more frequency lately.

I have noticed it in posted worksheet formulas also.


Gord Dibben Excel MVP

On Sun, 20 Mar 2005 17:41:23 -0500, "Tom Ogilvy" wrote:

That appears to be a bug in the google beta. It appears to add a hyphen
sometimes when you paste code.

Focus
 

Gord Dibben wrote...
True. I am finding I have to be very careful when copying from
postings.

The hyphen(s) are thrown in with more frequency lately.

I have noticed it in posted worksheet formulas also.
....

I wrote about this a week ago. It's unsafe to copy anything from Google
Groups beta *except* from *ORIGINAL* versions of postings which include
all the lovely NNTP tags above the body of the message. Those originals
seem to be literal text without any HTML interpretation, so HTML 'soft'
hyphens become visible.

Focus
 

Dave Unger wrote...
The reason I was asking - the Format line seems to work whether CLng
is
present or not. As I'm still on the steep part of the learning curve,
I probably wouldn't have realized the need for it.

Format appears to be smart enough to convert numeric text (i.e., stuff
for which IsNumeric returns TRUE and VarType returns vbString).
Therefore, no type conversions appears to be needed when calling
Format.

That said, Format(x, "00:00") seems to treat the second argument the
same as "00\:00", so just inserts a colon when it can convert x to a
number. Then the worksheet function TIMEVALUE applies Excel's date/time
semantics in which 9:99 would be converted to 9 hours 99 minutes = 10
hours 39 minutes, so automatically converted to 10:39.

If you're *ALWAYS* entering times without colons and without AM/PM, so
always in [h]hmm format where hours could range from 0 or 00 to 24,
then you'd be *MUCH* better off using simple *TEXT* *PATTERN* tests for
valid time entries.


Time.Value = Trim(Time.Value)
If Not(Time.Value Like "2[0-4][0-5][0-9]" _
Or Time.Value Like "[01][0-9][0-5][0-9]" _
Or Time.Value Like "[0-9][0-5][0-9]") Then
MsgBox "Invalid time entry: " & Time.Value
Time.Value = ""
Cancel = True
End If