Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
EnableEvents
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
EnableEvents
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) "Stefi" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
EnableEvents
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) "Stefi" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
EnableEvents | Excel Programming | |||
EnableEvents doesn't work | Excel Programming | |||
Application.EnableEvents | Excel Programming | |||
Application.EnableEvents help | Excel Programming | |||
application.EnableEvents | Excel Discussion (Misc queries) |