Enabling and Disabling Events

---

The Excel Application object has an EnableEvents property that you can use to enable or disable event triggers. Because VBA code, including event procedures, can cause events to be triggered, you might find situations where you will be required to disable events. For example, changing a cell’s value from VBA will trigger the Change events in all object levels. If you have code in the Worksheet_Change event procedure that modifies another cell, you must disable events to prevent Worksheet_Change from calling itself repeatedly. If the procedure continually called itself, Excel would either overflow its call stack (an internal record of the pending jobs within Excel) or run out of memory.

By default all events are enabled. To disable all events, add the following line of code to your VBA procedure:

Application.EnableEvents = False

To enable the events, use this line of code:

Application.EnableEvents = True 

Note 

This setting is for the entire Excel application, so setting it to False will affect all your open workbooks, not just the active workbook containing the code. Also, remember that Excel doesn’t restore the setting when your code ends, so be sure to set it back to True to reactivate the events.

The primary reason to disable events is to prevent an infinite loop of continuous events. For example, let’s say you have developed a timesheet and the maximum number of hours an employee is allowed to work is 40 hours per week. You can write the code to validate the cell contents whenever data is entered into the cell containing the total hours of work for each employee. In this example, you would monitor the Change event for the worksheet using a procedure named Worksheet_Change. Your procedure would check the user’s entry to verify that it is less than 40. If the entry exceeds 40, the procedure will display a message informing the user that he or she has exceeded the allowed hours of work and then clear the entry in the cell. The problem with this scenario is that when the cell contents are cleared, the VBA code generates a new Change event, so the event is executed again. This is not what you want to happen, so you need to disable events before you clear the cell, and then reactivate the events so that you can monitor the next user entry. The following Worksheet_Change event displays the required code to validate the users input in a range named Hours. For this example you can replace the range name Hours with a specific cell address to test this code in any workbook. Because this Event procedure refers to the worksheet level, the code needs to be added to the module of the worksheet that you want to trigger the Change event.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim VRange As Range
    Set VRange = Range("Hours")
    If Intersect(Target, VRange).Value > 40 Then 
        MsgBox "The weekly hours cannot exceed 40."
        Application.EnableEvents = False
        ActiveCell.Value = ""
        Application.EnableEvents = True
    End If
End Sub

Note 

Application.EnableEvents = False will affect all open workbooks; however, it does not affect events outside of the Excel Object Model. Events associated with ActiveX controls and User Forms will continue to occur.

---