Workbook & Worksheet Events in Excel VBA

• Share on Facebook.
• Share on Twitter.
• Share on Google+
• Share on LinkedIn
• Pin It!

What Are Events?

An Event occurs when certain actions are performed by the user or VBA. For example when you open a workbook the Workbook_Open event is triggered. When you (or your VBA) saves a workbook, the Workbook_BeforeSave event is triggered.

These events are built into Excel VBA and are executed automatically by Excel. You can write your own code which is also executed when these events occur. This code is known as an Event Handler.

In this article I’m going to look at events for worksheets and workbooks, but there are also events specific to the Excel application itself, user forms, and charts.

What Events Are Available?

For each object there are specific events available. An easy way to check is to let the VBA editor show you.

Worksheet Events

Right click on a sheet tab and choose ‘View Code’

This will open the VBA editor with your sheet module already selected. Below you can see that I have the Sheet1 module selected.

In the code window on the right hand side of the editor window, click on General and from the drop down click on Worksheet.

You’ll notice that the drop down on the right side has now changed, and is showing the SelectionChange event. The editor has also entered the Worksheet_SelectionChange event declaration for you.

If you click on the right hand drop down, you will see a list of all the events for the worksheet. Clicking on any of these will enter the event code declaration for you into the code window.

Workbook Events

For workbook events, double click on ThisWorkbook on the left hand side (in the Project Explorer)

and on the right hand side (the code window) select Workbook from the left hand drop down.

You should now have the declaration for the Workbook_Open event in your code window.

As with the worksheet, clicking the right hand drop down gives you a list of events for the workbook and clicking on these will enter the code declaration for that event.

Location of Event Code

Before we go any further let’s talk about where the event code must be stored. With ‘normal’ VBA code you’d create a new module and enter the code in there. But with events, VBA expects the code to be in specific places. If you put it in a ‘normal’ module, it won’t work.

If you want to write an event handler for a worksheet the code must be in the sheet module for the relevant sheet. Each sheet has its own module. Double click the sheet name in the Project Explorer in the VBA Editor to access the sheet module.

Likewise, workbook level code must be in the ThisWorkbook module.

Writing VBA for Worksheet Events

You can write whatever code you like and put it into the event procedure declaration. When an event occurs it is said to be raised and when this happens, Excel runs whatever code it finds associated with that event.

So let’s write some code to execute when the Worksheet_SelectionChange event is raised. We’re going to change the color of the currently selected cell (the ActiveCell) to green

Cells.Interior.Color = xlColorIndexNone

ActiveCell.Interior.Color = RGB(117, 173, 33)

OK so I can now make a cell a nice, bright green, but that’s not a huge lot of use I suppose. Let’s do something more useful, like indicate the currently selected cell by putting a border around the current row and the current column

Cells.Borders.LineStyle = xlLineStyleNone

ActiveCell.EntireColumn.BorderAround Weight:=xlThick, Color:=RGB(117, 173, 33)

ActiveCell.EntireRow.BorderAround Weight:=xlThick, Color:=RGB(117, 173, 33)

You could combine both pieces of code to change the color of the ActiveCell, and put a border around the current row and column.

Parameters

You may have noticed that the declaration for the Worksheet_SelectionChange event specifies a parameter, Target, that is passed into the Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

This parameter is a range and you can use it in your code to test things like how many cells are selected, or which column is selected, or some value or property of the selected range. I’m going to test how many cells are selected and if there are more than 1, I’m going to exit the sub. To do this I just add one line of code

If Target.Cells.Count > 1 Then Exit Sub

So my event code now looks like this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

Cells.Borders.LineStyle = xlLineStyleNone

ActiveCell.EntireColumn.BorderAround Weight:=xlThick, Color:=RGB(117, 173, 33)

ActiveCell.EntireRow.BorderAround Weight:=xlThick, Color:=RGB(117, 173, 33)

End Sub

If you use your mouse to select more than one cell, nothing happens because I am checking the Target parameter and can tell that there is more than 1 cell selected.

Other events pass in different parameters, but all are there for you to test/use as you see fit.

Useful Worksheet Events

Below are some useful worksheet events, but you can read more about the available events at Microsoft

• Worksheet_Activate
• Worksheet_BeforeDelete
• Worksheet_Change
• Worksheet_Deactivate

Writing VBA for Workbook Events

Let’s look at a couple of examples of workbook events. The first is Workbook_Open which is raised when, you guessed it, the workbook is opened. We could write something like this.

Private Sub Workbook_Open()

MsgBox "Welcome back, I missed you!"

End Sub

You can of course put whatever code you like in there and it will run when the workbook is opened.

But, hang on you say, what if macros are disabled?

Well yes, then the macro won’t run, but that is a topic for another day. Let’s for now assume that you have things set up so that you either are opening workbooks from Trusted Locations and the macros will run, or you have your users educated to know to enable macros on your workbook(s).

Next we want to do something when a user tries to add a sheet. Let’s say we want to ask them what they want to call the sheet. We can use a simple InputBox to ask them for the new sheet name, and rename the sheet for them.

Private Sub Workbook_NewSheet(ByVal Sh As Object)

Dim NewSheetName As String

NewSheetName = InputBox("What do you want to call this sheet?", "Please enter the new sheet name")

If NewSheetName <> "" Then Sh.Name = NewSheetName

End Sub

Note that I use Sh.Name = NewSheetname to change the name of the new sheet. Sh is the parameter passed into the Workbook_NewSheet event sub and is actually the new sheet object.

I use an IF statement to test if the user has entered anything. If they hit the ESC key or click Cancel, then a zero length string is returned, and this can’t be the sheet’s new name. So this

If NewSheetName <> ""

checks if a zero length string (nothing) has been entered. If it has, then I don’t try to rename the sheet.

Don’t forget that code for your workbook events must go into the ThisWorkbook module.

Useful Workbook Events

Some useful workbook events are listed below, and further info on workbook events can be found at Microsoft's MSDN site

• Workbook_BeforeSave
• Workbook_BeforePrint
• Workbook_AfterSave
• Workbook_SheetActivate
• Workbook_SheetCalculate
• Workbook_NewSheet
• Workbook_SheetChange
• Workbook_BeforeClose
• Workbook_Open

Further Reading

This link is for an article about 2010 Events, but is generally applicable to 2007/2010 and 2013.

Running VBA for Excel 2010 Events

Sample Code

All the code I've written is available in an xlsm, an xls (Excel 97-2003) or in text format.

Please download and have a play around with your own events. Let me know if you have any questions or get stuck, and I'd love to hear your comments below.

Please Share

If you know someone who could use this please click the buttons below to share it on Google+, LinkedIn, Facebook and Twitter.