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