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

"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

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

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

"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.

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

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

hi,
try this

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

bye, ste

Thanks ste,

The Cancel=True was the key

Dave