User form textbox message for input error

Hello,

I have a form that will have several textbox fields. In the fields where a User enters a date, I have a macro that checks if the input is a valid date. I can get a message to display "invalid date", but when you click "OK" on the message box the curser automatically tabs to the next field. What I want is if the input fails the validation test, the curser returns to the original date field so the user can correct the error. How can I keep the curser from automatically tabbing to the next field?

Here's the code I have so far:

#
Private Sub Purch_Date_AfterUpdate()


Dim MyDate As String

Me.Purch_Date.Value = Format(Me.Purch_Date.Value, "mm-dd-yyyy")

MyDate = Me.Purch_Date.Value

If IsDate(MyDate) = False Then

MsgBox ("Invalid Date")
Purch_Date.SetFocus
Exit Sub
End If


End Sub
#

The Purch_Date.SetFocus does not seem to fix the problem. I have tried disabling autotab to no avail.

Thanks in advance for the help!

I've had similar problems with it. Use another userform to show a message or just add a label control and inform the user that way.

Create an variable BounceBack. set BounceBack = True right after your MsgBox. The enter routine for the next control could have the line
if BounceBack then BounceBack= False : PunchDate.SetFocus

I figured out the solution to my problem. I was using the afterupdate event. I should use the BeforeUpdate event to validate the event and set cancel to True as shown in the code example below.

Please Login or Register  to view this content.

My thanks to those who responded with suggestions.