Every time you do something in Excel, an event happens. Selecting cells, changing cells, clicking a commandbar button: all these are events. Excel exposes some, but not all, events in VBA and you, as a programmer, can write code to react to these events.

Where To Put Event Code

Event code goes in class modules. For purposes of this post, we’ll be dealing with those special class modules named ThisWorkbook, Sheet1, etc. When you open the ThisWorkbook code window in the VBE, there are two dropdown boxes at the top of the window. The left contains a list of objects associated with that code window and the right contains a list of the events that are available to you.

When you select an event using the dropdowns, VBA inserts the Sub and End Sub statements for you. There are default events for each object. When you select Worksheet from the left dropdown (in the Sheet1 code window), VBA inserts the Sub and End Sub for the SelectionChange event. There is no way to change which event is the default, which is a shame. I use the SelectionChange event once for every 50 times I use Change.

Let’s look at some commonly used events:

Workbook_Open

This event fires whenever the workbook containing the event is opened. It’s often used to do set up work for your application, such as hiding sheets or creating custom commandbars. There are no arguments to Workbook_Open.

Workbook_BeforeClose

This event fires when you close the workbook containing the event. Actually, before you close it. The major flaw with this event become evident when you try to close an unsaved workbook. The event fires, then Excel gives the user the chance to cancel the close. This means that your clean up code will run, but the user may not actually close the workbook. The event has one argument called Cancel. You can set this arguement equal to True in your code and it has the effect of cancelling the close. You can then write your own code to close the workbook.

Workbook_Activate

This event fires when the workbook becomes the ActiveWorkbook. I use this and the Deactivate event to build and destroy commandbars instead of Open and BeforeClose. I don’t want my custom commandbars to be available to the user if they have an unrelated workbook open where the macros would be irrelevent (and maybe do some harm).

Workbook_Sheet???

Many of the sheet events that are available to you are also available at the workbook level. This means that instead of the event applying to only one sheet, it applies to all the sheets. You can use the Worksheet_Change event for whenever a cell changes on a particular worksheet, or you can use the Workbook_SheetChange event for when a cell changes on ANY sheet.

Worksheet_Change

This event fires whenever a cell’s value changes on the worksheet. You can write code to test the value entered in a cell, for example, replacing the built-in Data Validation feature. It’s also commonly used to apply conditional formatting to cells where you need more than three conditions. The Target argument is a reference to the cell(s) that were changed. Remember that this event doesn’t fire as a result of a formula recalculating, it requires input into a cell.

Worksheet_SelectionChange

This event fires when the user selects a cell. It’s argument is the same as the Change event, that is, a reference to the cell(s) that was(were) selected. For both the Change and SelectionChange events, you can limit the events to a particular range. These examples limit the event so that the code runs when one cell was selected, cell J10 was selected, and any cell in column A was selected:

If Target.Cells.Count = 1 Then
If Target.Address = "$J$10" Then
If Target.Column = 1 Then

Worksheet_FollowHyperlink

This is one of my favorites. It fires when the user clicks a hyperlink. It can be quite useful if your workbooks move around a lot and the hyperlinks don’t always seem to point to the correct path. In that case, you create a hyperlink that points to the cell in which it resides (effectively doing nothing) and use this event to navigate to the correct document. The Target argument here is a Hyperlink object, so be sure you don’t get it confused with other arguments named Target like in the Change event (that one’s a Range object).

Worksheet_Calculate

This event fires whenever the worksheet is recalculated. If you want to test the result of a formula (for which the Change event won’t fire), you can use this event.

Recursive Event Calls

Ooh, that sounds complicated. Don’t worry about it, I just like to throw around big words like recursive. What this means is that your code calls events too. If you change a cell via code, the Worksheet_Change event will fire and that code will run. It’s not a bad thing, unless you don’t want it to happen.

The Application object has a property called EnableEvents which you can use to prevent events from firing while your code is running. If, for instance, you used the Worksheet_Change event to test a cell and, if the cell’s value is invalid, to change the cell to something else, the event would fire again from within itself. When a procedure (sub or function) calls itself, it’s called recursion. You could potentially call the Change event over and over until you run out of memory. Not good. You can wrap your code like this to make sure it doesn’t happen.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Target.Value <> 5 Then
Target.Value = "Invalid"
End If

Application.EnableEvents = True

End Sub

Setting this property to False (then back to True at the end) prevents the Change event from being called from itself (when Target.Value is set to Invalid).

In What Order Do The Events Fire

Chip Pearson has a handy workbook on his download page called EventSeq. He’s put message boxes in all the events so you can see their firing order.

Is That All There Is To Events

Yep! Just kidding, there’s a lot more to events than just this. There are Application level events. Just like Workbook_Sheet??? type events, you can make events that cover every workook. External Data tables also have events, but they are not so easily exposed (you have to create a custom class module). Userforms and their controls also have lots of events. This post should get you started, and you can continue by experimenting with the events to see what they do.