TOGGLE BUTTON on userform problem

As one of many subroutines in my project, there is a simple event handler which triggers off a messagebox at the end of a loop. Structurally, the code looks like:

Private Sub ToggleButton1_Click()
For each sh in WorkSheets
<do something>
k = k & something & vbcrlf
Next
MsgBox k
End Sub

Now, for some strange reason, when the ToggleButton is clicked, the MsgBox comes up twice each time. This is irritating, to say the least and in my attempts to stop the nuisance I have tried the event variants Private Sub ToggleButton1_Change and Private Sub ToggleButton1_Enter-to no avail. While the Change event acts the same way as the Click event (and shares the double movement problem), the Enter event fires the MsgBox only once as desired but does not permit repeat successive runs of the code.


For what it is worth, let me also remark that, when the code is transplanted to a fresh userform and tested in its new enviroment, the above problem disappears: the MsgBox springs up once and no more.


If anyone has the disgnostic eye for this problem, I welcome him with thanks.

David.

Hi David,

Without any idea of what your sheet loop does I can only guess something
triggers the ToggleButton1_Click to fire a second time (resetting toggle
buttons would do that). Which would also suggest your entire loop also gets
repeated.

One way to prevent code within the event running, without disabling events,
is to set a module or global level flag, eg

Dim bExit as boolean
Private Sub ToggleButton1_Click()
if bExit then
Exit Sub
End if
bExit = true
On error goto errH:
For each sh in WorkSheets
<do something>
k = k & something & vbcrlf
Next
MsgBox k
errH:
bExit = false
End Sub

Regards,
Peter T

"davidm" <[email protected]> wrote in
message news:[email protected]...
>
> As one of many subroutines in my project, there is a simple event
> handler which triggers off a messagebox at the end of a loop.
> Structurally, the code looks like:
>
> Private Sub ToggleButton1_Click()
> For each sh in WorkSheets
> <do something>
> k = k & something & vbcrlf
> Next
> MsgBox k
> End Sub
>
> Now, for some strange reason, when the ToggleButton is clicked, the
> MsgBox comes up *twice* each time. This is irritating, to say the least
> and in my attempts to stop the nuisance I have tried the event variants
> * Private Sub ToggleButton1_Change* and * Private Sub
> ToggleButton1_Enter*-to no avail. While the Change event acts the same
> way as the Click event (and shares the double movement problem), the
> Enter event fires the MsgBox only once as desired but does not permit
> repeat successive runs of the code.
>
>
> For what it is worth, let me also remark that, when the code is
> transplanted to a fresh userform and tested in its new enviroment, the
> above problem disappears: the MsgBox springs up once and no more.
>
>
> If anyone has the disgnostic eye for this problem, I welcome him
> with thanks.
>
> David.
>
>
> --
> davidm
> ------------------------------------------------------------------------
> davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
> View this thread: http://www.excelforum.com/showthread...hreadid=487645
>

Thanks, Peter, for your input. The global variable (bExit in the example) can ensure the event fires but once only if it (the global variable) is used elsewhere in other procedures in the module. Do you suggest, I introduce the variable in all these other procedures (15 of them) and set bExit =false next to Exit Sub?

Regards.

David.

"davidm" wrote in message
>
> Thanks, Peter, for your input. The global variable (bExit in the
> example) can ensure the event fires but once only if it (the global
> variable) is used elsewhere in other procedures in the module. Do you
> suggest, I introduce the variable in all these other procedures (15 of
> them) and set bExit =false next to Exit Sub?

Possibly, depending on what you are doing, Eg -
Say you have code to loop through sheets resetting the up/down state (value)
of activeX togglebuttons as relate to each sheet. You don't want the code in
the click event to do anything if the value changes.

In each click event
If bExit Then Exit Sub

In the main routine, as per original example, bExit = True before the loop
and false at the end.
You could also set bExit = True in all routines that might trigger events
you don't want processed, eg
If bExit Then Exit Sub
bExit = True
'code
bExit = False

Be careful to ensure bExit is reset False when done, normally best to
include an error handler as in original example.

Maybe, guessing, you only want click event processed depending on the state
of the new value, eg

If myToggle.value = true Then
'do normal code even if bExit = True
Elseif bExit Then
' do nothing
Else
' some reset code
End if

Regards,
Peter T