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
581 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
When asked for what could blow his programme off course, Harold Macmillan famously said: "Events, dear boy, events" (or did he?). Much the same can be said of your program in Excel VBA.
Whenever you open, save or close a workbook, print something out, click on a cell, type something in or right-click or double-click, events happen - and to each and every one of these events you can react with judiciously written code.
Suppose that you want to make sure that when someone using your workbook opens it, a macro automatically takes them to the correct worksheet. You could do this with the following event-handling macro:
Private Sub Workbook_Open()
'go to the correct worksheet on opening
Worksheets("ImportantSheet").Select
End Sub
So: how do you write macros like this?
The first step is to decide whether your macro is going to handle an event to do with the entire workbook, or a single worksheet. Here are some examples:
Scope of event | Examples |
---|---|
Workbook | Opening, saving or closing a file, printing anything |
Worksheet | Clicking on a cell, changing a cell's value, right-clicking on a cell |
A fuller list of examples is given later in this blog. For our example, we want to attach code to the workbook, so you need to double-click on ThisWorkbook:
Double-click on the workbook in the code window (or right-click on it and choose to view its code).
This is almost the only time in Excel VBA that you should write code in anything other than a module.
You can now choose to attach code to the workbook itself (there's actually no other choice, so it's annoying that this isn't selected automatically for you):
Click on the drop arrow where it says General, and choose Workbook.
You can now choose the particular event you want to handle by clicking on the right-hand drop arrow:
Choose from the list of possible events which can happen in a workbook's life.
Note that on this occasion there was actually no need to do this, as Excel guessed that it was the workbook's Open event that you wanted to intercept and "handle".
You can now type in any code that you want to run when a user opens your workbook! When you save your file and re-open it, then all things being well your code will run.
Whenever an event begins with the word Before it means that you can either react to the event which is about to occur and - if desired - prevent it happening. Here's an example which stops anyone closing workbooks if they haven't filled in a version control number.
Option Explicit
'holds the current version number
Private VersionNumber As Integer
Private Sub Workbook_Open()
'read in the version number in cell A1
Worksheets(1).Select
VersionNumber = Range("A1").Value
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim NewVersionNumber As Integer
'if version number hasn't changed, can't close
Worksheets(1).Select
NewVersionNumber = Range("A1").Value
If VersionNumber = NewVersionNumber Then
'user hasn't changed version number - abort
MsgBox "Must update version number first"
Cancel = True
End If
End Sub
Here's what this macro does:
In the above code, the line:
'cancel this event
Cancel = True
will cancel whichever event it was which triggered the macro (in this case, saving the workbook).
Now that we've seen how to create events and cancel them, let's look separately and in more detail at workbook and worksheet events.
This page looks at some of the most common workbook events, with some examples of what you might do for them.
As a reminder, here's how to create a workbook event:
The steps to creating a workbook event are listed below.
The steps are:
The most useful events available for a workbook are as follows:
Event | Use |
---|---|
BeforeClose | Prevent closure of workbook if some condition isn't true |
BeforePrint | Prevent printing if (eg) data hasn't been filled in |
BeforeSave | Prevent a user saving incomplete workbooks |
NewSheet | React to a user inserting a new worksheet |
SheetCalculate | Run when a user presses F9 to calculate a worksheet |
The rest of this page contains some examples of macros that you might write - they are somewhat tongue-in-cheek.
The following amusing little macro will stop your colleague printing out your workbook on Tuesdays and display the following amusing (?) message:
The message you'll see when you try to print this workbook on a Tuesday.
Here's the code which would make this work (or rather, not work):
Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Weekday(Date) = vbTuesday Then
'don't allow printing on Tuesdays
MsgBox "Sorry - the printer goes " & _
"to see its mother-in-law on Tuesdays"
Cancel = True
End If
End Sub
You can't prevent a user adding a new sheet to a workbook, but you can react to what they've just done and undo it!
The sort of message you could display when someone adds a worksheet.
Here is some code to do this - note that it's up to you to guess that in this context Sh must refer to the worksheet you've just added!
Private Sub Workbook_NewSheet(ByVal Sh As Object)
'user has just inserted a worksheet - delete
'it quietly
Application.DisplayAlerts = False
Sh.Delete
Application.DisplayAlerts = True
'explain why
MsgBox "Sorry - this workbook is full"
End Sub
Now that we've seen some events for workbooks, let's consider events at the worksheet level.
You can attach code to events for any worksheet in the same way as for a workbook.
Here's a quick summary of how to attach an event to a worksheet:
The steps for how to attach code to a particular worksheet are shown below.
The steps to follow are:
There are fewer events in a worksheet's life than a workbook's - does this mean they're less interesting?
The most useful events available for a worksheet are as follows:
Event | Use |
---|---|
Change | This event fires whenever you change any cell's value |
SelectionChange | Runs whenever you select a different cell or cells |
BeforeDoubleClick | Whenever you double-click in the centre of a cell (rather than on the edge of it) |
BeforeRightClick | Whenever you right-click in the centre of a cell (rather than on the edge of it) |
The rest of this page contains some examples of macros that you might write - as for the workbook examples, they are not meant to be taken seriously (although they do perfectly illustrate what is possible).
Suppose that you want to react to a user clicking on a particular cell. You can do this using the SelectionChange event:
We want to stop anyone clicking on the cell shown - and punish anyone who does!
The code to prevent anyone selecting the cell shown - or any range containing it - could look like this:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'if the range just select doesn't intersect
'with the broken one, that's fine
If Intersect(Target, Range("C2")) Is Nothing Then Exit Sub
'otherwise, vindictively delete all worksheet
Cells.Clear
MsgBox "It did warn you ..."
End Sub
In this case, if you select any range containing C2 the nasty little macro will erase all of the cells in the current worksheet.
The really scary thing about macros like this one is that in Excel you can not undo the results of running a macro (although strangely you can in Word).
A common requirement is to do something when a user changes the value of a cell. In the example below, we prevent the input of odd numbers in a cell:
If a user types in an odd number, we want to react to it.
Some code to react to a user typing in an odd number could be:
Private Sub Worksheet_Change(ByVal Target As Range)
'if this is a single cell, and it's C2 ...
If Target.Cells.Count = 1 Then
If Target.Row = 2 And Target.Column = 3 Then
'don't allow odd numbers
If Target.Value Mod 2 = 1 Then
MsgBox "No odd numbers allowed"
Range("C2").Value = Target.Previous.Value
End If
End If
End If
End Sub
Here we check if the user has changed the value of a single cell in row 2, column 3 (we could also have checked if the cell's Address property had equalled $C$2, noting that this is case-sensitive).
It is difficult in VBA to reset a cell's value back to the previous value for a macro like this - some fairly advanced ideas for how to do this can be found here and here.
You may now have reached this point in this online tutorial and be smugly thinking that your user can not now do anything untoward. However, users can bypass macros - read on!
Sadly (for you, as a developer) it is easily possible to bypass macros which run automatically.
When you open up any workbook containing macros, if your security level is set to Medium (the default and most sensible value), you'll see a message like this:
A typical dialog box when you open a workbook containing macros.
If your user chooses the default button to disable macros, there's not much you can do about it!
An alternative way to bypass macros is by holding down the SHIFT key just at the point that you're about to open a file.
If you're distributing your workbook to clients, colleagues or other third parties, there are various solutions to this problem - but none is that satisfactory:
You can ask them nicely to enable macros in your workbook.
You can copy the workbook into a trusted folder (but you'll need to have administrator rights to their machine to do this).
You can digitally sign the macro (but the recipient will still be able to refuse to accept it).
Basically, there is no way of forcing a third party to run your macros - which if you think about it is as it should be!
Although this blog is mainly about Excel VBA, I thought I'd briefly show how events work in various other Microsoft Office applications. I've used Office 2010 for all of the examples on this page, but the ideas would work just as well in Office 2007.
Microsoft Word follows pretty much the same pattern as Microsoft Excel:
You can double-click on ThisDocument to attach code to an event for the document.
The list of events available isn't as rich as it is for Excel:
The list of events doesn't include BeforeClose, for example.
There is a way to create additional events for Word using something called an event sink, but it's scary stuff, and you'll need to understand classes to work out what's going on.
PowerPoint doesn't allow you to attach code to events easily:
There is a presentation open, but you can't see that in Project Explorer.
However, as for Word there is a way to attach code to events for a presentation by creating something called an event sink (see above).
Even if you do create a PowerPoint event sink, you still won't have truly solved the problem, as you still need something to run a macro to create the event sink in the first place!
The whole of Access form and report design is based around the concept of events:
This diagram shows the events available for a simple label control (a bit of text stuck on a form).
Whereas events in Excel, Word and PowerPoint are optional extras, you won't be able to write VBA code in Access forms or reports without using them all the time.
The events which can occur in an Outlook session are shown below:
Want to create a rule for how new mail should be treated? Don't bother with the Outlook rules - why not create your own in VBA!
Outlook macros are all held in a single file with an .OTM extension.
As the above examples show, handling events in Excel VBA is - as is so often the case - easier than doing the same thing in other MS Office applications.
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.