Capture Excel application events - event sinks
It's not for the faint-hearted, but there is a way to capture application events (such as someone trying to create a new workbook) in Excel, using something called an event sink.  This blog explains how you might go about creating an event sink.

This blog is part of our Excel VBA tutorial online.  For classroom training for businesses and individuals, see our introductory and advanced VBA training courses.

Posted by Andy Brown on 06 March 2012 | 1 comment

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Event Sinks to Capture Application Events

Event sinks are, sadly, useful; "sadly" because they're also quite hard to understand.  I would only recommend reading this if you're happy with classes, or if you're the sort of person who can copy and paste examples happily without understanding them (I know I'm not).

An Example of When to Use an Event Sink

Suppose that you have written an Excel system, and you want to tie it down so that your users can't create new workbooks, for example.  Impossible?  No!

List of application events

You can get at the list of application-level events - it's just not that easy!

 

The way forward is to create what's called an event sink, to capture any application-level events.

Creating the Event Sink

The first thing to do is to create a new class:

Creating class module

Insert a class module as shown here.

 

You can then use the Properties window to give this class a name:

Class name property

Here we've changed the name of our class from Class1 to ExcelEventCapture.

 

Now within your new ExcelEventCapture class create a new variable to hold a reference to the running copy of Excel:

Option Explicit

'a private property of the class, to contain a reference

'to the current copy of Excel

Private WithEvents ExcelApp As Excel.Application

The keyword WithEvents is vital here, as the whole point of your class is to capture Excel events.

Now choose to assign code to the initialisation event for this class:

Choosing Class from dropdown

Select Class from the drop down list of objects (it's the only one) at the top left of the module window.

You can now complete the initialisation code stub to get your complete class:

Option Explicit

'a private property of the class, to contain a reference

'to the current copy of Excel

Private WithEvents ExcelApp As Excel.Application

Private Sub Class_Initialize()

'point the private property above to the running copy of Excel

Set ExcelApp = Excel.Application

ExcelApp.Visible = True

End Sub

When you create a new instance of this class, a variable called ExcelApp will automatically be created to refer to a copy of Excel.

Using the Event Sink

Your event sink won't work until you create a new instance of it.  Annoyingly, you must do this before anything else in your system.  Here's an example of how to do this.  In a new module, type:

Option Explicit

'refers to event sink

Public ExcelEvents As ExcelEventCapture

Public Sub CreateEventSink()

'set up the event sink

Set ExcelEvents = New ExcelEventCapture

End Sub

When you run the CreateEventSink procedure above, you'll be able to use your event sink. 

However, note that your event sink will apply to the new copy of Excel created programmatically, and not to the one in which you are running the code.  For this reason it is often best to use event sinks from other applications or from Excel add-ins.

The Exciting Bit - Handling Application-Level Events

Now that everything's set up, you can return to your class and choose application-level events to handle.  First choose the object:

The Excel application object

Choose the Excel object that you've created, using the left-hand side of the declarations section of your class module.

Now use the right-hand side of the declarations section of the class module to choose the event:

The list of application events

Here we're going to block the new workbook event.

 

For our example, we'll close down any workbook the user tries to create:

Private Sub ExcelApp_NewWorkbook(ByVal Wb As Workbook)

'stop user creating a new workbook

Wb.Close savechanges:=False

MsgBox "Sorry - you can't create workbooks in this system!"

End Sub

You can now play about with the other application-level events to block other actions. 

 

This blog has 1 comment

Comment added on 30 April 2012 at 12:01 GMT
This is most understandable text I ever seen about subject. But it still do not work for me.
I placed this example in my AddIn.
May I ask some questions:
1/ When Class Initialization code will be run after it was changed? For my experiments I had to restart Excel every time when I changed some things. Breakpoint inside Class_Initialize was never been reached.
2/ Where I have to place text of CreateEventSink function? And where I have to call it?
I placed it in separate module and call it in Class_Initialize. What I lost here? 

Thanks in advance for any suggestions.


Reply from Andy Brown
Firstly, apologies for the fact that you won't have received a reply about your commment. For some reason our system has lost who posted this. If you could contact us letting us know how you made your comment, we'd be grateful so we can fix this glitch in our systems. Regarding your questions, you should put the CreateEventSink code inside a separate module (not a class module) - I've amended the blog to make this clearer. You can then run the CreateEventSink routine just like any other macro. This will create a new instance of the ExcelEventCapture class, which will run this class's initialisation event code.

A full-blown discussion forum is being built for this site, which will allow you once more to add comments and discussion threads.