Macro executing twice??

[WLMPilot]

I am trying to test the input value of textboxes in a userform. The correct
value to be entered is 1, 2, 3, or 4. There are four textboxes that execute
the MSValidate routine. The routines work and the incorrect entries are
caught but the problem is that both routines (see below) execute twice before
actually returning to the textbox to accept correct entry. I had placed
msgboxes throughout both routines to follow the flow.

Because I was not familiar with the CHANGE() event, I had inserted an
IF-THEN statement in the MSValidate routine to catch a negative number, not
knowing that as soon as I entered the "-" part of the neg number, the CHANGE
event executed.

If you can direct me to a better way to trap entries other than a 1-4,
please let me know.


Private Sub TextBox6_Change()
Dim TB As Integer
TB = 6
MSValidate (TB)
End Sub


Sub MSValidate(TB As Integer)
Dim MSQty, Config, Morphine, Ans, Ans1, NegNum As Integer
MSQty = Worksheets("Items").Range("Z3") ' Get MAX quantity of Morphine
Select Case TB
Case 6
Morphine = Val(TextBox6.Value)
Case 7
Morphine = Val(TextBox7.Value)
Case 8
Morphine = Val(TextBox8.Value)
Case 9
Morphine = Val(TextBox9.Value)
End Select
NegNum = 0
'MsgBox Morphine
If Morphine <= 0 Then
Config = vbOKOnly + vbExclamation
Ans1 = MsgBox("Invalid Quantity. Please enter a 1 - " & MSQty, Config)
NegNum = 1
GoTo Resetboxes
End If
If Morphine > MSQty Then
Config = vbOKOnly + vbExclamation
Ans = MsgBox("Max quantity for Morphine is " & MSQty, Config)
If Ans = vbOK Then
GoTo Resetboxes
End If
End If
Resetboxes:
Select Case TB
Case 6
TextBox6 = ""
TextBox6.SetFocus
Case 7
TextBox7 = ""
TextBox7.SetFocus
Case 8
TextBox8 = ""
TextBox8.SetFocus
Case 9
TextBox9 = ""
TextBox9.SetFocus
End Select
End If
Morphine = 0
NegNum = 0
End Sub


Any ideas of why both execute twice?

Thanks,
Les

 

[Bob Phillips]

Try this

Private Sub TextBox6_Change()
Dim ReEntry As Boolean
Dim TB As Integer

If Not ReEntry Then

ReEntry = True

TB = 6
MSValidate (TB)

ReEntry=False
End If
End Sub

 

[JLatham]

You're keying off of a change in TextBox6 which calls the MSValidate()
routine. In MSValidate() there is a condition that will then set TextBox6 =
"". That would trigger the TextBox6_Change() event again. I suggest
changing that code to:

Private Sub TextBox6_Change()
Dim TB As Integer
TB = 6
Application.EnableEvents = False
MSValidate (TB)
Application.EnableEvents = True
End Sub

A potential undesirable side effect of this is that you have a run-time
error in the MSValidate() routine, Excel won't respond to events (as a button
click) until Application.EnableEvents is set to true again (or you close and
reopen Excel). You can issue the Application.EnableEvents = True statement
in the VBE Immediate window if this does happen during development.

 

[Rick Rothstein]

Correct, the TextBox6="" statement is triggering the Change event again. In
addition, that second Change event calls the MSValidate subroutine again as
well. The reason this does not degenerate into an infinite loop is because
once the TextBox is empty, setting it to the empty string does not change
it, so the looping stops after two iterations. If the OP is interested in
changes that are numerical, we could stop the looping by just checking for
the empty string directly...

Private Sub TextBox6_Change()
Dim TB As Integer
If TextBox6.Text <> "" Then
TB = 6
MSValidate (TB)
End If
End Sub

 

[JLatham]

MLMPilot - Bob's solution is the better of the two: it achieves the same goal
and doesn't run the risk of leaving event processing disabled in the event of
an error in downstream code.

 

[JLatham]

See Bob Phillip's solution below. It's the better of the two generally, but
you may need to add similar code to any other TextBox#_Change() events that
may exist and that may be triggered by changing their values within the
MSValidate() code.

 

[Joe User]

Dim ReEntry As Boolean

Shouldn't that be:

Static ReEntry As Boolean

Otherwise, I think ReEntry is initialized to zero each time the Change macro
is entered.

(At least, that is true for a Worksheet_Change macro.)


----- original message -----

 

[Joe User]

Bob's solution is the better of the two: it [...] doesn't run the risk
of leaving event processing disabled in the event of an error in
downstream code.

But when it is corrected (Static ReEntry instead of Dim ReEntry), I think it
runs the risk of effectively disabing the TextBox6_Change macro -- at least,
the portion executed when ReEntry is False.

Moreover, as you noted elsewhere in this thread, it requires similar code to
be implemented in all other event macros that might be triggered (or
re-triggered) unintentionally when MSValidate is executed.

(In fact, ReEntry might need to be a global variable if you want the same
effect as disabling EnableEvents.)

I think that error recovery must be dealt with in either case.

And if that is done correctly, I think that disabling EnableEvents is the
cleaner solution -- unless the intention is to permit other events to be
triggered when MSValidate is executed.

(Not intended, based on my brief reading of the original posting.)


----- original message -----

 

[WLMPilot]

Thanks. I knew it had to be something staring me in the face.

Les

 

[WLMPilot]

Thanks, Something simple staring right at you.

Les

 

[WLMPilot]

Could you explain how the ReEntry works?

Les

Try this

Private Sub TextBox6_Change()
Dim ReEntry As Boolean
Dim TB As Integer

If Not ReEntry Then

ReEntry = True

TB = 6
MSValidate (TB)

ReEntry=False
End If
End Sub

--

HTH

Bob




.

 

[JLatham]

Rick Rothstein also came up with an interesting solution that doesn't use a
flag like Bob Phillips or the EnableEvents trick and may be best of all --
still may require changes to other TextBox#_Change event processors in
similar fashion.

Which all goes to show once again that there is definitely more than one way
to beat a dead horse with Excel.

Bob's solution is the better of the two: it [...] doesn't run the risk
of leaving event processing disabled in the event of an error in
downstream code.

But when it is corrected (Static ReEntry instead of Dim ReEntry), I think it
runs the risk of effectively disabing the TextBox6_Change macro -- at least,
the portion executed when ReEntry is False.

Moreover, as you noted elsewhere in this thread, it requires similar code to
be implemented in all other event macros that might be triggered (or
re-triggered) unintentionally when MSValidate is executed.

(In fact, ReEntry might need to be a global variable if you want the same
effect as disabling EnableEvents.)

I think that error recovery must be dealt with in either case.

And if that is done correctly, I think that disabling EnableEvents is the
cleaner solution -- unless the intention is to permit other events to be
triggered when MSValidate is executed.

(Not intended, based on my brief reading of the original posting.)


----- original message -----

MLMPilot - Bob's solution is the better of the two: it achieves the same
goal
and doesn't run the risk of leaving event processing disabled in the event
of
an error in downstream code.

.

 

[JLatham]

ReEntry is a simple boolean value and can have only 2 values: True or False.
The "Not ReEntry" is pretty much same as If ReEntry=False
When the code is initially called, ReEntry is false and it will then set
that flag to True, and it stays True until after the MSValidate() routine is
called, so if this routine is called again by the change in MSValidate() to
TextBox6, the code within the IF Then End IF block won't be executed again.

Take a look at the post by Rick Rothstein earlier - it may offer an even
cleaner solution, which like the other two may need to be set up for the
other TextBox processes that may get triggered during MSValidate() processing.