[steve]

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
===================================

[Tom Ogilvy]

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
===================================

[steve bell]

Tom,

As always - you came through AND so quickly!
Thank you!

My ignorance is showing as I used the following:

Private Sub TextBox54_BeforeUpdate(ByVal Cancel As
MSForms.ReturnBoolean)
Dim phn
phn = TextBox55
If Len(phn) < 7 Or Len(phn) < 10 Then _
Cancel = True

End Sub

wasn't sure how to use:
if "not validated" then _
Cancel = True

steve

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Tom Ogilvy]

What you did is what you meant - I was just representing if the criteria was
not passed, then set cancel = true - which is the way you wrote your code.

Regards,
Tom Ogilvy

"steve bell" wrote in message
...
Tom,

As always - you came through AND so quickly!
Thank you!

My ignorance is showing as I used the following:

Private Sub TextBox54_BeforeUpdate(ByVal Cancel As
MSForms.ReturnBoolean)
Dim phn
phn = TextBox55
If Len(phn) < 7 Or Len(phn) < 10 Then _
Cancel = True

End Sub

wasn't sure how to use:
if "not validated" then _
Cancel = True

steve

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[steve bell]

Tom,

Thanks! Again!

Understanding is slowly coming... Have been working with it and am
getting the desired results.

as always...

steve

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!