Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro executing twice??
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro executing twice??
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 "WLMPilot" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro executing twice??
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. "WLMPilot" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro executing twice??
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 -- Rick (MVP - Excel) "JLatham" wrote in message ... 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. "WLMPilot" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro executing twice??
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. "Bob Phillips" wrote: 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 "WLMPilot" wrote in message ... 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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro executing twice??
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. "JLatham" wrote: 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. "WLMPilot" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro executing twice??
"Bob Phillips" wrote:
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 ----- "Bob Phillips" wrote in message ... 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 "WLMPilot" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro executing twice??
"JLatham" wrote:
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 ----- "JLatham" wrote in 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. "Bob Phillips" wrote: 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 "WLMPilot" wrote in message ... 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 . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro executing twice??
Thanks. I knew it had to be something staring me in the face.
Les "Rick Rothstein" wrote: 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 -- Rick (MVP - Excel) "JLatham" wrote in message ... 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. "WLMPilot" wrote: 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 . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro executing twice??
Thanks, Something simple staring right at you.
Les "Bob Phillips" wrote: 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 "WLMPilot" wrote in message ... 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 . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro executing twice??
Could you explain how the ReEntry works?
Les "Bob Phillips" wrote: 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 "WLMPilot" wrote in message ... 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 . |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro executing twice??
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. "Joe User" wrote: "JLatham" wrote: 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 ----- "JLatham" wrote in 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. "Bob Phillips" wrote: 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 "WLMPilot" wrote in message ... 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 . . |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro executing twice??
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. "WLMPilot" wrote: Could you explain how the ReEntry works? Les "Bob Phillips" wrote: 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 "WLMPilot" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Executing a macro | Excel Programming | |||
Executing Macro Help | Excel Programming | |||
executing a macro within another | Excel Discussion (Misc queries) | |||
Preventing macro from .xlt from executing in a .xls | Excel Programming | |||
What macro is executing? | Excel Programming |