Application.Enableevents = False doesn't stop all events?

[PaulW]

I'm trying to make a userform. And one thing I want it to do is change the
value on a togglebutton to True.

But I have some code on that togglebuttons_click event which triggers when
the value is changed (not only when it clicks?) which recalls the macro that
changed the value.

So what I end up with is an infinate loop, when all I wanted to do was
change the togglebuttons value. I've tried adding in Application.EnableEvents
= False, but the togglebutton_click event still triggers. Is there a way
round that?

 

[OssieMac]

Hi Paul,

I have run into the problem previously and the following is an example of
how I handled it. (The posted code is only used for testing). I will be
interested if anyone has a better method because I personally think that it
is messy.

Private Sub ToggleButton1_Click() still runs but the If statement excludes
the code associated with it and I have not had it go into an eternal loop.

Also the code has to be all in the one module with the variable dimensioned
in the Declarations section at the top of the module.


Dim ctrlToggleButton1 As Boolean

Private Sub ToggleButton1_Click()

'False is used to run code because
'ctrlToggleButton1 is False by default
'when you open the workbook.
If ctrlToggleButton1 = False Then
MsgBox "ToggleButton1 = " & ToggleButton1.Value
End If

End Sub

Private Sub MyToggle()

ctrlToggleButton1 = True

If ActiveSheet.ToggleButton1.Value = False Then
ActiveSheet.ToggleButton1.Value = True
Else
ActiveSheet.ToggleButton1.Value = False
End If

ctrlToggleButton1 = False

End Sub

 

[PaulW]

The way I keep having to work around it is to have a invisible label that I
change to 1 at the start of the macro, and 0 at the end. If the label reads
as 1 then the togglebutton click event doesn't call the macro.
I'm sure a public variable is a better way to do it though.

I was just hoping that I could have some way of stopping it. In one instance
I wanted to change the value on 120 togglebuttons (I can't remember why) and
it was annoying me that it had to run through 120 click events that did
nothing.

 

[Chip Pearson]

Paul,

EnableEvents doesn't apply to controls on a user form since these are
managed by VBA, not the Excel Application directly. You can create your own
EnableEvents switch as a module level Boolean variable and write code to
turn it TRUE or FALSE when needed. Then you'd code your events (such as the
Click event) to exit immediately if your own EnableEvents is TRUE. E.g.,

Option Explicit

Public MyEnableEvents As Boolean

Private Sub CommandButton1_Click()
If MyEnableEvents = False Then
Exit Sub
End If
MyEnableEvents = False
ToggleButton1.Value = Not ToggleButton1.Value
MyEnableEvents = True
End Sub


Private Sub ToggleButton1_Change()
If MyEnableEvents = False Then
Exit Sub
End If
MsgBox "Normal Code"
'''
' normal code goes here
''
End Sub

Private Sub UserForm_Initialize()
MyEnableEvents = True
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)