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?

 

3 Comments

  • March 15, 2011 - 1:09 am | Permalink

    Thanks. I am having an issue i believe is related to this. Is this an appropriate place to ask for guidance?

    If so, my issue is that I am trying to build a relatively straightforward spreadsheet/application that includes hierarchies of ActiveX checkboxes, i.e. a checkbox for a category of items toggles all checkboxes for items in sub-category on or off when clicked (as as per common usage), so there is code in cbo_main_checkbox_click and cbo_sub_checkbox_click events that potentially changes the Value property for the other check-box(es). For some reason (???!) if the code causes a change in the Value property, e.g. from True to False, it fires the associated object’s Click event (even though no click has technically occurred!). For the record it also fires the associated Change event as well (which makes sense).

    Adding Application.EnableEvents = False and Application.EnableEvents = True statements at beginning and end of event related sub-routines has no effect on how the code executes, i.e. event code still fires inappropriately :-(

    I understand how to gypsy-rig my way out of this with a bunch of flags and tests; my goal here is to figure out why the behavior I am seeing is even happening, and >>IF<< it is behaving as designed or intended by the all-knowing (or is it know-it-all!) Microsoft gods, then what is the best practice

    If it matters, I am using Excel 2007 and VB(A) 6.5 on a Win7/64-bit machine.

    Thanks…

  • Matt
    March 19, 2011 - 1:03 pm | Permalink

    Sounds like very odd behaviour. Do you have a mockup of the sheet you could send on for me to have a look at? I’d be more than happy to test it out and see why this issue is happening – it might be useful material for another blog post (with your permission, that is).

    You can send files to me at m dot richardson dot home at googlemail dot com.

    Mat

  • August 8, 2011 - 12:20 pm | Permalink

    I’ve got the same problem.
    Did you find a solution or the reason of this problem?

    Thanks!

  • Leave a Reply

    Your email address will not be published. Required fields are marked *