This page has 1 thread | Add post

Picture depicting wise owl guides and tutorials

Event Sinks to Capture Application Events

Written by Andy Brown

In this tutorial

An Example of When to Use an Event Sink
Creating the Event Sink
Using the Event Sink
The Exciting Bit - Handling Application-Level 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!

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:

Insert a class module as shown here.

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

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:

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:

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:

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.

Previous
tutorial

Loops

Previous
part

Event-handling for VBA in other applications

Top of page

Next
part

Next
tutorial

Debugging

You can learn more about this topic on the following Wise Owl courses:

This page has 1 thread

Add a new post

From:	duggie
When:	15 Feb 25 at 15:03

An alternative approach would be to add the following code to ThisWorkbook:

Option Explicit

Private Sub Workbook_Deactivate()

If ActiveWorkbook.Name <> ThisWorkbook.Name Then ActiveWorkbook.Close

End Sub

Add a new post

Online tutorials

Tutorials on VBA

Events

Event Sinks to Capture Application Events

An Example of When to Use an Event Sink

Creating the Event Sink

Using the Event Sink

The Exciting Bit - Handling Application-Level Events

Head office

London

Manchester