If you use beforeupdate to do the validation, all you have to do is set
Cancel = true to stay in the box

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
if "not validated" then _
Cancel = True

End Sub

Regards,
Tom Ogilvy


"steve" wrote in message
...
I have a form in my VBA (Excel 97 SR2, Win2000) and am using the below
code
to format the data entry after update. Also to reselect the Textbox if
the
entry is incorrect (I will check later to determine if it is a numeric
entry).

My question: I am using SendKeys to re-activate the TextBox, but have
heard
that SendKeys is not reliable. Have tried
QForm.TextBox55.SetFocus
to no avail. (Searched Google and got the SendKeys solution)

Can anyone help make this work?

Thanks in advance!!!

steve
====================================
Private Sub TextBox55_AfterUpdate()
Dim phn ' note not defined so that it shows as number on sheet
phn = QForm.TextBox55
If Len(phn) < 7 Or Len(phn) < 10 Then
phn = MsgBox("Phone number incorrect." _
& Chr(10) & "ReEnter number.", vbOKOnly)
Application.SendKeys ("+{tab}")
Exit Sub
ElseIf Len(phn) = 7 Then
phn = Format(TextBox55, "000-0000")
ElseIf Len(phn) = 10 Then
phn = Format(TextBox55, "(000) 000-0000")
End If
QForm.TextBox55 = phn
End Sub
===================================