Suppressing Events In UserForms
|In most VBA code, you can use the
Application.EnableEvents property to prevent Excel from calling event
procedures. This often used in the Change event of a worksheet if the Change
event itself changes a cell value. Without EnableEvents set to False, the
user changes a cell, which triggers the Change event, which changes a value,
which triggers the Change event, which changes a cells, which triggers the
Change event, and so on ad nauseam until VBA decides to terminate the loop
to prevent an "out of stack space" runtime error. EnableEvents set to
False will prevent this looping.
Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False ' ' change code here ' Application.EnableEvents = True End Sub
However, Application.EnableEvents does not apply to controls on a UserForm. Changing a control's value will usually cause and event for that control to be execute. For example, change the value of the ListIndex of a ListBox control will cause the ListBox's Change and Click events to fire. Since you are changing the ListIndex in code, you probably don't want the events to run.
To suppress events in a form, you can create a variable at the form's module level called "EnableEvents" and set that to False before changing a property that will cause an event to be raised.
Public EnableEvents As Boolean
Private Sub UserForm_Initialize() Me.EnableEvents = True End Sub Sub Something() Me.EnableEvents = False ' some code that would cause an event to run Me.EnableEvents = True End Sub
Then, all of the controls on form should have a test if that variable as their order of business in any event code. For example,
Private Sub ListBox1_Change() If Me.EnableEvents = False Then Exit Sub End If MsgBox "List Box Change" End Sub
You can declare the EnableEvents as Private if only procedures with that form need to suppress events. However, if you have forms that are programmatically linked together, such UserForm2 adding an item to a ListBox on UserForm1, you should declare the variable as Public and set it for another form with code like the following:
UserForm1.EnableEvents = False ' ' change something on UserForm1 ' UserForm1.EnableEvents = True
The primary difference between the EnableEvents property and code shown above and the Application.EnableEvents property is that with a UserForm EnableEvents, all control on the form must have code to exit if EnableEvents is True. In other words, all the form's controls must cooperate and respect the setting of EnableEvents.