BeforeUpdate Event triggers twice

[drinkwater.mark]

I am getting a curious result when coding a ms excel 2003 form.

--------------------------------------------------------------------------------------------------------------------------------------
Private Sub BookNumberTextBox_BeforeUpdate(ByVal Cancel As
MSForms.ReturnBoolean)

'MsgBox "Please ensure that the Docket Number being input is six
digits long.", _
vbOKOnly, "Correct Data Input"

If Len(Me.BookNumberTextBox) <> 6 Or Len(Me.BookNumberTextBox) = 0
Then

Me.lblError = "Please ensure that the Docket Number being input
is six digits long."
Cancel = True

End If

End Sub
--------------------------------------------------------------------------------------------------------------------------------------

The code above is being triggered twice when a user tries to tab out of
the control.

Basically, if the if statement is true, i dont want the user to be able
to leave the textbox. This is taken care of...

When i had the error message as a msgbox, it would be triggered
twice... i fixed this by having an error label... but it is messing
with my tabindex... for some reason when the before update event
passes... it is skipping the next box in the tabindex and going to the
one after... i am assuming this has something to do with the two
triggers.

Has anyone seen this problem before??

thanks
Mark

 

[Bob Phillips]

Mark,

I just ran a simple test and did not get two runs of that code when I
exited. Have you checked that all the tabindexes are in the correct
sequence, and do you have other event code that might trigger it.

If all else fails you could try this technique

Dim fEventOn As Boolean

Private Sub BookNumberTextBox_BeforeUpdate(ByVal Cancel As
MSForms.ReturnBoolean)

If fEventOn Then
fEventOn = False
'MsgBox "Please ensure that the Docket Number being input is six
digits long.", _
vbOKOnly , "Correct Data Input"

If Len(Me.BookNumberTextBox) <> 6 Or _
Len(Me.BookNumberTextBox) = 0 Then

Me.lblError = "Please ensure that the Docket Number " & _
"being input is six digits long."
Cancel = True

End If

fEventOn = True

End If

End Sub

but make sure that you initialise Feventon to True in the userform activate
or iniotialise procedures.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)