MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Userform Field Validation

  Asked By: Fern    Date: Nov 02    Category: MS Office    Views: 1045

I am using a userform to collect data to append data to a datalist.
My aim is to validate &/or format the user input as each field is
exited. If the data is not in a valid format use a simple messsage
box to advise the user, clear the entry and for the focus to remain
in that control until the user inputs a valid entry.

Initial checks using the following procedure is not retaining the
focus when an erroneous input is made?
I would also like to add a audible alarm when the message box is
Any guidance would be gratefully received

Private Sub txtCourseDate_AfterUpdate()
If IsDate(txtCourseDate.Value) Then
txtCourseDate = Format(txtCourseDate, "dd/mm/yyyy")
MsgBox ("Invalid date format, please use 'dd/mm/yyyy'")
txtCourseDate.Value = ""
End If
End Sub



2 Answers Found

Answer #1    Answered By: Kathleen Adams     Answered On: Nov 02

The order of firing events can be a bit confusing sometimes.

What's happening is that the afterupdate event is firing because you've
given the focus  to another control.. Say a command button. Now that control
has the focus. Your code is then setting the focus to the text box  correctly
but in order for the afterupdate event to happen you hvave to have left the
text box... Excel says... Angon.. I'm somewhere else or else I couldn't do
the afterupdate.. I'll go back there.... And puts you back in the control
that had the focus in order for the afterupdate event to fire.

Try putting the Me.txtCourseDate.SetFocus in the Enter event of the control
that now has the focus.

Answer #2    Answered By: Dixie Jacobs     Answered On: Nov 02

I have tried putting the setfocus statement in the following control.
That does indeed reset the focus  back to the previous control, but
because the Afterupdate event has already been triggered I receive the
msgbox dialog again and then the focus moves back to the following

However, your suggestion prompted me to look at the BeforeUpdate event,
and with the following amended code this works purfickly....

Private Sub txtCourseDate_BeforeUpdate(ByVal Cancel As
If IsDate(txtCourseDate.Value) Then
txtCourseDate = Format(txtCourseDate, "dd/mm/yyyy")
MsgBox ("Invalid date  format, please use 'dd/mm/yyyy'")
txtCourseDate.Value = ""
Cancel = True
End If
End Sub

Didn't find what you were looking for? Find more on Userform Field Validation Or get search suggestion and latest updates.