Tag Archives: events

Excel Tips Visual Basic For Applications (VBA)

Excel VBA: The Worksheet_Change event and Application.EnableEvents

I was creating some code which automatically multiplied any numerical value in a range by 1,000 by using the Worksheet_Change event.  This is how the code looked:-

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range(“A1:A10”)) Is Nothing Then
Target = Target * 1000
End If
End Sub

Try entering this yourself and entering a value into cell A1 and you’ll find you get an ‘overflow’ error.  The reason behind this is that, each time the code multiplies the cell value by 1,000 it essentially ‘changes’ the cell and therefore the Worksheet_Change event fires again, and this continues until Excel throws a wobbly and can’t cope anymore.

So, how do we fix this?  The key is to change the Application.EnableEvents property.  If you change the property to false, you’re telling Excel not to allow any events to fire at all until you tell it otherwise.  Here’s how the code should look to prevent the error:-

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range(“A1:A10”)) Is Nothing Then
Target = Target * 1000
End If
Application.EnableEvents = True
End Sub

In the first line of the subroutine we tell excel to ignore any events that fire.  We then run the code we want (in this case change the cell contents), and then re-enable events firing if something in the specified range changes.  Easy enough, isn’t it?