This page has 0 threads | Add post

Picture depicting wise owl guides and tutorials

Many people around the world enjoy and benefit from our guides. If you're one of them - and can afford to - please make a small donation to help keep this and future blogs, exercises, skills assessment tests, tips, tutorials and videos freely available to everyone!

You can learn more about this topic on the following Wise Owl courses:

Workbook Events in Excel

Written by Andy Brown

In this tutorial

Creating Workbook Events
The Main Workbook Events Available
Example: Preventing Printing on Tuesdays
Example: Displaying a Misleading Message when Adding a Worksheet

This page looks at some of the most common workbook events, with some examples of what you might do for them.

Creating Workbook Events

As a reminder, here's how to create a workbook event:

The steps to creating a workbook event are listed below.

The steps are:

Double-click on ThisWorkbook for the file to which you want to attach code (or right-click on it and choose to view its code).
Click on the drop arrow next to General, and choose Workbook (the only option in the list!).
From the right-hand drop arrow, choose the event to which you want to attach code.

The Main Workbook Events Available

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.

Example: Preventing Printing on Tuesdays

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

Example: Displaying a Misleading Message when Adding a Worksheet

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

Previous
tutorial

Loops

Previous
part

An Introduction to Events in Excel VBA

Top of page

Next
part

Worksheet Events in VBA

Next
tutorial

Debugging

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on VBA

Events

Workbook Events in Excel

Creating Workbook Events

The Main Workbook Events Available

Example: Preventing Printing on Tuesdays

Example: Displaying a Misleading Message when Adding a Worksheet

Head office

London

Manchester