Hi all,
Small piece of validation on text box. If it's not 8 characters, clear the
text box and place the insertion point in the same text box.
The following in placed in the After_Update event:

Dim PN As String

PN = txtpolicynumber.Text

If Len(Policynumber) < 8 Then
Msgbox "Invalid Entry. Must be 8 digits long", vbOKOnly + vbCritical,
"Data Validation"
txtpolicynumber.Text = ""
txtpolicynumber.SetFocus
End If

This works *ok* in a command button, but *not* in the text box's
after_update event. It places the cursor in the next text box. I can only
assume that it is placing the focus in the required text box, but it is not
cancelling the tab event, and still moving to the next object.

Any ideas on how to cancel the tab? :-s

TIA,

Piers

Hello Piers 2k,

You need to place the macro in the Exit event and use Cancel. You can
copy and paste the updated macro into your project.


Code:
--------------------
Private Sub txtPolicynumber_Exit(ByVal Cancel As MSForms.ReturnBoolean)

L = Len(txtPolicynumber.Text)

If L 0 And L < 8 Then
Msgbox "Invalid Entry. Must be 8 digits long", vbOKOnly + vbCritical, "Data Validation"
Cancel = True
With txtPolicynumber
.SelStart = 0
.SelLength = L
.SetFocus
End With
End If

End Sub

--------------------

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=483052

"harpscardiff"

is this starting to sound repetitious. Same advice you received yesterday.


--
Regards,
Tom Ogilvy


"Leith Ross" wrote
in message ...

Hello Piers 2k,

You need to place the macro in the Exit event and use Cancel. You can
copy and paste the updated macro into your project.


Code:
--------------------
Private Sub txtPolicynumber_Exit(ByVal Cancel As
MSForms.ReturnBoolean)

L = Len(txtPolicynumber.Text)

If L 0 And L < 8 Then
Msgbox "Invalid Entry. Must be 8 digits long", vbOKOnly + vbCritical,
"Data Validation"
Cancel = True
With txtPolicynumber
.SelStart = 0
.SelLength = L
.SetFocus
End With
End If

End Sub

--------------------

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile:
http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=483052