[IncludeBorders/top.htm]

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.

For example, 
    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.

 
     
   
     
[IncludeBorders/bottom.htm]