EnableEvents

[Stefi]

Hi All,

I have a CheckBox1 with Sub CheckBox1_Click(). In a certain case I want to
change the value of linked cell of CheckBox1 through VBA and in this case
triggering Sub CheckBox1_Click() is not required. I tried
Application.EnableEvents = False, but it had no effect. I solved the problem
by setting/resetting a public variable, but I'd like to know the reason why
Application.EnableEvents = False is ineffective in such cases.

Thanks,
Stefi

 

[Bob Phillips]

Because the checkbox is not part of the Excel application object, so it
doesn't apply. It is either part of the Office forms object.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Chip Pearson]

EnableEvents, being part of the Application object, affects only things that
are part of Excel (Application, Workbooks, and Worksheets). It doesn't
affect things that are part of VBA (which is used by many applications
besides Excel) not Excel (userforms, controls on forms, etc). The standard
method is to create your own EnableEvents variable for the form and then set
it to false if you don't want events to run. Then, in any event procedure,
test that variable and, if False, get out of the event code immediacy. You
will probably want to structure your error handling so that the enable
events flag is set to True if an error occurs.

For example, in the code module for UserForm1, use something like

''''''''''''''''''''''''''''''''''''''''''''''
' If True, do normal event handling. If
' False, exit any event procedure immediately.
''''''''''''''''''''''''''''''''''''''''''''''
Public pEnableEvents As Boolean

Private Sub UserForm_Initialize()
''''''''''''''''''''''''''''''''''''''''''''
' Initialize enable events to True.
''''''''''''''''''''''''''''''''''''''''''''
pEnableEvents = True
End Sub


Private Sub ComboBox1_Change()
''''''''''''''''''''''''''''''''''''''''''''
' If events are suppressed, get out
' immediately.
''''''''''''''''''''''''''''''''''''''''''''
If pEnableEvents = False Then
Exit Sub
End If
''''''''''''''''''''''''''''''''''''''''''''
' Normal change code goes here.
''''''''''''''''''''''''''''''''''''''''''''
End Sub

Friend Sub InitializeComboBoxes()
''''''''''''''''''''''''''''''''''''''''''''
' We don't want the Change event to run
' when we are loading the initial values
' to the ComboBox so set pEnableEvents to
' False.
''''''''''''''''''''''''''''''''''''''''''''
pEnableEvents = False
''''''''''''''''''''''''''''''''''''''''''''
' Error handling should ensure that
' pEnableEvents gets restored to True.
''''''''''''''''''''''''''''''''''''''''''''
On Error GoTo ErrH:
''''''''''''''''''''''''''''''''''''''''''''
' Code here to load initial values to comboboxes.
' After loading values, restore to True.
''''''''''''''''''''''''''''''''''''''''''''

''''''''''''''''''''''''''''''''''''''''''''
' Error handler at end of procedure to
' ensure events are restored to True.
''''''''''''''''''''''''''''''''''''''''''''
ErrH:
pEnableEvents = True
End Sub




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