Most Valuable Professional

View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.


Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

The best tool to optimise your Excel model!

What has changed?

PerfectXL Compare, for Spreadsheet Comparison
This Excel compare tool is fast, reliable, and easy to use. Compare two Excel files now and see for yourself!
Home > English site > Articles > Disable Events
Deze pagina in het Nederlands

Control When Events Are Handled


Events are a powerful aspect of Excel programming. They enable you to make your application respond to user actions such as entering data into cells or clicking the print button. If your application uses events, you will probably also need to be able to control whether or not an event executes its code or not (e.g. to avoid event looping or to enable your code to do things you are preventing your user to do through the user interface).


There are several ways to disable event code. One of them is to use Application.EnableEvents=False. But that will disable all application events, including event handlers add-ins may need. If your code crashes, events stay disabled! Another disadvantage is that it will not work for Userform events. Another one is by using a global variable, which you check against inside the event module. But this is not really good programming practice (although I admit I use that technique myself too). Below I will show you a more general approach, using a boolean variable inside the class module that contains the events. As an example I'll use the Thisworkbook module, but in principle any class module will do (the Thisworkbook module, Sheet modules and modules behind userforms are in fact class modules).

Let's say you want to prevent your users closing your workbook. So you have written a Workbook_BeforeClose routine in the Thisworkbook module:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    MsgBox "You are not allowed to close this file!", vbInformation + vbOKOnly
    Cancel = True
End Sub

But of course you want to be able to close the file using your own code. Add a public variable to the top of the Thisworkbook module:

Option Explicit

Public NoEvents As Boolean

And inside the BeforeClose event, check the value of that variable:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If NoEvents Then Exit Sub
    MsgBox "You are not allowed to close this file!", vbInformation + vbOKOnly
    Cancel = True
End Sub

Of course now you need to put this to use. In any subroutine that may need to close the file:

Sub CloseMe()
    ThisWorkbook.NoEvents = True
End Sub

Of course if you're not closing the file, but need to disable other events, remember to set the NoEvents back to False:

    ThisWorkbook.NoEvents = False

The big advantage of this technique over using
is that should your code be reset (e.g. due to the user clicking the End button on a runtime error you didn't catch), then the variable NoEvent is reset to False and your events will work as expected. It will also give you finer control as to what happens, since you can switch off a single event, just by adding more public variables:

Public NoCloseEvent As Boolean
Public NoPrintEvent As Boolean


All comments about this page:

Comment by: greg (2-12-2009 04:49:56)


Your page really corresponds to the problem I am experiencing: I used enableEvents too much and finally crashed the code.
Now no events is detected, even if I reset manually application.enableEvents to true.

Could you please advise me on how to get events detected again permanently?

Many thanks


Comment by: Jan Karel Pieterse (2-12-2009 11:36:36)

Hi Greg,

Normally, turning on events using enableevents=true should suffice. Try cleaning your code using Rob Bovey's code cleaner at


Comment by: Greg (2-12-2009 12:42:23)


Unfortunately, now that Excel has crashed with application.enableevents being set to false, this value seems to reset itself to false automatically.

If I use a simple subroutine that sets application.enableevents to true (checked with a msgbox before and after), running it a second time without doing anything else gives me an initial value set to false again.

Is there an option somewhere in Excel that controls the event detection? Maybe another variable to reset?

I will try the code cleaner, thanks for the link



Comment by: Jan Karel Pieterse (2-12-2009 23:01:03)

I suspect a problem with Excel or with an addin you have installed. Check my Startup problems page:


Comment by: Greg (3-12-2009 02:19:06)


I tried all steps from your startup page as well as the startup cleaner, but the event detection still reverts to false automatically, even if I set it to true manually.

Any advice? Please help !


Comment by: Greg (3-12-2009 02:22:34)

If this can be of any help for troubleshooting, note that if I open previous versions of my file, then the event detection works fine, so the error should really be within the given file (another global variable set to a wrong value??).

Obviously I can't just come back to the previous file: too much data has been changed on the sheets.


Comment by: Jan Karel Pieterse (3-12-2009 02:28:21)

Hi Greg,

In that case there is definitely something wrong with your file. Either there is code turning off events without you realising it (do a project-wide search on that keyword and put a breakpoint on each, then turn events on using the immediate pane).
Or your file has acquired a corruption, in which case I recommend reading


Comment by: Greg (3-12-2009 03:27:35)

Ok, great, I finally worked it out through the switching of formulas calculation to manual.
It boiled down to the worksheet_calculate subroutine: I had not realised it was called that often.

Many thanks for your help, great spirit!


Comment by: Peterg (22-2-2010 04:13:11)

I have several text boxes on a form which respond to the MOUSEDOWN EVENT and run code.
However these same text boxes sometimes have to accept keyboard input, which means I have somehow to disable the MOUSEDOWN and let them respond to a click event,not branching to code. Is This Possible
Regards Peterg


Comment by: Jan Karel Pieterse (22-2-2010 07:28:05)

Hi Peter,

Not sure I understand the question. What does the mousedown event do, that should not be done when the user wants to enter something in the textboxes?


Comment by: avisok (16-2-2016 16:34:30)



Comment by: DavidU (8-11-2018 18:12:21)

Hi Jan, great article, but I have a question - "Another one is by using a global variable, which you check against inside the event module. But this is not really good programming practice (although I admit I use that technique myself too). Below I will show you a more general approach, using a boolean variable inside the class module that contains the events."

I must be missing something, but how is your general approach different from the "not good programming practice" method you mention?

Thank you, regards,



Comment by: Jan Karel Pieterse (9-11-2018 11:44:16)

Hi David,

What I am trying to explain is using a publicly declared variable in a normal module, which makes it a globally available variable. As opposed to a locally declared, module-level variable (inside the module containing the event code).


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here:

Please enter your name (required):

Your e-mail address (optional, will only be used to inform you when your comment is published or to respond to your question directly):

Your request or comment (max 2000 characters):

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

I give permission to process this data and display my name and my comment on this website accoring to our Privacy Policy.