Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
549 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Written by Andy Brown
In this tutorial
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).
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.
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.
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.
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.
You can learn more about this topic on the following Wise Owl courses:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.