Excel VBA – Event Procedures

Most of the time when you write a macro, it’s one that you will run by clicking on a button on the Quick Access Toolbar, or from a button on the Ribbon, or even directly within your Visual Basic Editor. Whichever method you use, the code is running on demand.

If you write code that other people may end up using, from a workbook on a shared drive for example, relying on users to run code is very much hit and miss. Some will be reliable and click on the right button at the appropriate moment. Others…..forget it.

So how can you force the user to run code at the right time?

What you need is an EVENT PROCEDURE. Unlike a normal sub routine that has to be manually kick started, an EVENT PROCEDURE automatically runs if the user does something within your workbook.

EVENT PROCEDURES take place at either workbook or individual worksheet levels within a workbook. The types of events vary at each level.

When you look at your PROJECT EXPLORER window you will see the names of any sheets in the workbook as well as something called THISWORKBOOK.

Finding ThisWorkbook in the VBA Editor project explorer window

It is here, in either (or both) at worksheet and/or workbook level that you will add your EVENT PEOCEDURES, not in modules as you do with normal sub routines.

Let’s start with WORKBOOK level procedures.

Double click THISWORKBOOK. This will open a blank sheet to write code, but we don’t want to use this. Click on the drop down on the right hand side above the work area and select WORKBOOK.

Getting to the Workbook scripting area

After clicking on WORKBOOK you should see this;

Default event at Workbook level

The editor automatically selects an event. Nothing you can do about this. It doesn’t mean you have to do anything with this, it’s just the default event to appear at workbook level.

There are a number of events that you can choose from. To see the full list go to the top right hand corner and click on the dropdown.

Workbook level event options

Use the scrollbar to view all the events available to you at Workbook level.

There are also events that run at WORKSHEET level. Double click on any of the sheets in your workbook and you get a blank sheet for code, identical to what happened when we wanted to create an event at workbook level. Where it says GENERAL, click on the drop down and this time select WORKSHEET.

Selecting Worksheet level

As with WORKBOOK events, the editor will display a default event. In the case of WORKSHEET it’s SELECTION CHANGE.

Default event at Worksheet level

If that’s not the event you want, click on the drop down on the right hand side where you can see SELECTION CHANGE. There are not as many options at this level as there were at WORKBOOK level, but still enough to keep you entertained.

Worksheet level event options

Again, use the scroll bar to see all available options.

Whether it is an event at WORKBOOK or WORKSHEET level, simply add your code as normal. Unless you know your events inside out and the variables they require, don’t try to get creative editing the opening line i.e. Private Sub… into the event you want. Pick the event you want from the drop down list and delete whichever one you don’t need from the editor sheet. If you try to edit the opening line, chances are you’ll dig yourself into an extremely deep hole from which you will probably never escape.

The most common mistake people make when starting to use events is to type in a command line that does the event. If you are not sure what I mean here are a couple of typical mistakes as examples;

Typical event errors by beginners

In a workbook called MyWorkbook the user wants something to happen when they open it. Unfortunately,  a line has been added that opens the very book they just opened. Don’t do it.

On a NEWSHEET event, whenever a user adds a new sheet, the code then adds yet another new sheet to the workbook and prompts the user to name this sheet rather than the one the user added.

Now…these two may seem like minor issues, but you don’t need to write an action to do what has already happened. Remember, it is the opening of the workbook by the user that triggers some code, so there is no need to write code that opens the workbook that has just been opened. In the same way, when someone clicks on the NEW SHEET button or right clicks and selects INSERT, you don’t want to add yet another blank sheet…the user has just created the event that triggers some code to run.

By all means, when a workbook is opened, open one or two more as these may be required. Effectively, you are making sure that the user is provided with all the workbooks they need to complete a task, and therefore eliminating any risk that the user forgets to open the others.

And that is really what you are doing with events in many cases – eliminating risk;

• Before closing a workbook make sure a copy of the workbook is saved in the correct folder in the correct drive
• Before saving changes to a workbook make sure all cells that need to be filled in have been
• If a new sheet is added, or a new version of a workbook is created, set the correct naming convention for sheets and workbooks etc. the possibilities are almost endless

An interesting example is one by Tom Urtis, getting a workbook to delete itself. http://www.atlaspm.com/toms-tutorials-for-excel/toms-tips-for-excel-workbook-suicide/

Or if you want to have a bit of fun and annoy people, try this;

VBA fun…strictly for geeks 😉

Highly annoying!

So don’t be afraid of using EVENT PROCEDURES. They can be very useful (ignoring the annoying example above) and can ensure that what you want to happen with your workbook actually does, without having to rely on users remembering to do things, which more often than not, doesn’t happen.