Introduction
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).
Example
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
ThisWorkbook.Close
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
Application.EnableEvents=False
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