I'm having a problem with my validation I set. Basically if policy
number textbox is not 8 digits long then msgbox retry. It should let me
go back to the textbox and update . The problem I am having is, I click
retry and it moves to next textbox and I dont know why

Below is the code:

Any ideas what is wrong?

Thanks



Code:
--------------------

Public Sub txtpolicynumber_AfterUpdate()
Dim Policynumber, Msg, Style, Title, Response, CheckRetry As String


Msg = "Invalid Entry. Must be 8 digits long"
Style = vbRetry + vbCritical
Title = "Data Validation"
Response = Msgbox(Msg, Style, Title)
CheckRetry = vbRetry
Policynumber = txtpolicynumber.Value


If Len(Policynumber) < 8 Then
Response = Msgbox(Msg, Style, Title)
txtpolicynumber.SetFocus
End If

End Sub

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


--
harpscardiff
------------------------------------------------------------------------
harpscardiff's Profile: http://www.excelforum.com/member.php...o&userid=25960
View this thread: http://www.excelforum.com/showthread...hreadid=482848

because the movement out of the textbox is still pending.

Try using the Exit event:

Public Sub txtpolicynumber_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim Policynumber, Msg, Style, Title, Response, CheckRetry As String


Msg = "Invalid Entry. Must be 8 digits long"
Style = vbRetry + vbCritical
Title = "Data Validation"
Response = Msgbox(Msg, Style, Title)
CheckRetry = vbRetry
Policynumber = txtpolicynumber.Value


If Len(Policynumber) < 8 Then
Response = Msgbox(Msg, Style, Title)
'txtpolicynumber.SetFocus
Cancel = True
End If

End Sub

--
Regards,
Tom Ogilvy


"harpscardiff"
wrote in message
news:harpscardiff.1y50by_1131384606.0756@excelforu m-nospam.com...

I'm having a problem with my validation I set. Basically if policy
number textbox is not 8 digits long then msgbox retry. It should let me
go back to the textbox and update . The problem I am having is, I click
retry and it moves to next textbox and I dont know why

Below is the code:

Any ideas what is wrong?

Thanks



Code:
--------------------

Public Sub txtpolicynumber_AfterUpdate()
Dim Policynumber, Msg, Style, Title, Response, CheckRetry As String


Msg = "Invalid Entry. Must be 8 digits long"
Style = vbRetry + vbCritical
Title = "Data Validation"
Response = Msgbox(Msg, Style, Title)
CheckRetry = vbRetry
Policynumber = txtpolicynumber.Value


If Len(Policynumber) < 8 Then
Response = Msgbox(Msg, Style, Title)
txtpolicynumber.SetFocus
End If

End Sub

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


--
harpscardiff
------------------------------------------------------------------------
harpscardiff's Profile:
http://www.excelforum.com/member.php...o&userid=25960
View this thread: http://www.excelforum.com/showthread...hreadid=482848

Thanks for your reply Tom. I understand what you’re saying, but th
code you added don’t work, it doesn’t prompt the user at all. Where a
before, it use to prompt the user and then continue to the next tex
box. I need it to work as you tab to the next textbox.

Thanks

--
harpscardif
-----------------------------------------------------------------------
harpscardiff's Profile: http://www.excelforum.com/member.php...fo&userid=2596
View this thread: http://www.excelforum.com/showthread.php?threadid=48284

I didn't change anything in the code that would change its behavior in terms
of prompting the user.

--
Regards,
Tom Ogilvy

"harpscardiff"
wrote in message
news:harpscardiff.1y6a5y_1131444003.1324@excelforu m-nospam.com...

Thanks for your reply Tom. I understand what you're saying, but the
code you added don't work, it doesn't prompt the user at all. Where as
before, it use to prompt the user and then continue to the next text
box. I need it to work as you tab to the next textbox.

Thanks.


--
harpscardiff
------------------------------------------------------------------------
harpscardiff's Profile:
http://www.excelforum.com/member.php...o&userid=25960
View this thread: http://www.excelforum.com/showthread...hreadid=482848