reload form

[ricardovilla]

i have a form that loads when the word doc opens up. i have some error checking for a textbox. it seems to be fine, so if i put an invalid date, i get the msgbox "not a valid date", but when i click on okay. the entire form goes away.

what am i missing? i have the form where it checks the textbox, without clicking the okay/submit button. i want it to be almost interactive, where it checks the value of the textbox before the user can go into the next field. but again. when i do enter in an invalid date, i get my msg, but i do not have a chance to re-enter the date again so it can validate again.



Private Sub txtDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)

dateCheck = IsDate(txtDate.Value)

Do While dateCheck = False
If dateCheck = False Then MsgBox ("This is not a valid date.")
txtDate.SetFocus
End
Loop

End Sub

[fumei]

1. you have duplicate instructions here.

Do While datecheck = False

means do this IF datecheck = False

Fair enough, but then you follow that with

If datecheck = False

Ummm, it is only going to action the IF if datecheck is False, i.e. you have already established it is False.

There is no need for the Do While loop - especially as there is no looping going on anyway.

If datecheck = False will do it. Or better yet, use Not - see below.

2. Obviously your OK button has an unload instruction ("click on okay. the entire form goes away"). Fair enough, that is normal.

You are using _Exit to test. Exit is a procedure that will execute instructions before the actual change in focus (you exit something and enter something else). So your instruction:

txtDate.SetFocus

does indeed execute (focus is set to txtDate)....but then _Exit finishes by....Exiting txtDate.

That is not going to work - as you know. Have you looked in Help? For your own sake, look up help on Enter and Exit procedures. However, if I understand correctly what you want, here is how it will work.
Private Sub txtDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = True
' the above means that focus will NOT move
' you set this first

If Not IsDate(txtDate.Value) Then
MsgBox ("This is not a valid date.")
' make textbox blank
txtDate.Text = ""
Else
' if value IS a date, make Cancel = False
' which DOES move focus to the next control
Cancel = False
End If
End Sub


3. Please use the VBA tags to post code. Thanks.

[mdmackillop]

I would use BeforeUpdate. Watch out for IsDate. It's a bit tricky. 111/1/1 is recognised as a date because it's in the right format

Private Sub txtDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Not IsDate(txtDate) Then
MsgBox ("This is not a valid date.")
txtDate.Text = ""
Cancel = True
End If
End Sub

[fumei]

Agree on the use of IsDate.

Agree also on using BeforeUpdate if this is a required field.