Enable Events

It is important to turn off event handling in some event procedures to prevent unwanted infinite recursion. For example, if a worksheet Change event procedure changes the worksheet, it will trigger the Change event and run itself again. The event procedure will change the worksheet again and trigger the Change event again, and so on.

If only one event procedure is involved, Excel 2007 will usually detect the recursion and terminate it after about 100 cycles. If more than one event procedure is involved, the process can continue indefinitely or until you press Esc or Ctrl+Break enough times to stop each process. It is even possible that Excel 2007 will crash, depending on how much RAM is available and what other code is doing.

For example, there could be a Calculation event procedure active as well as a Change event procedure. If both procedures change a cell that is referenced in a calculation, both events are triggered into an interactive chain reaction. That is, the first event triggers the second event, which triggers the first event again, and so on. The following Change event procedure makes sure that it does not cause a chain reaction by turning off event handling while it changes the worksheet. It is important to turn event handling back on again before the procedure ends:

Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Range("A1").Value = 100 Application.EnableEvents = True End Sub

Application.EnableEvents = False does not affect events outside the Excel object model. Events associated with ActiveX controls and user forms, for example, will continue to occur.

0 0

Post a comment