This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Event-handling macros in Excel Visual Basic Part two of a five-part series of blogs
Want to do something when a user opens a workbook, or stop them clicking on a particular cell? You need to learn how to attach code to Excel workbook or worksheet events! Introduction to Handling Events in Excel VBA Code Workbook Events (this blog) Events for a Particular Worksheet Bypassing Macros Considerations for Other MS Office Application Events This series of blogs is part of our Excel VBA online tutorial. If you want to learn more, have a look at our classroom-based courses in VBA macros and/or Excel.

Event-handling macros in Excel Visual Basic

Part two of a five-part series of blogs

Want to do something when a user opens a workbook, or stop them clicking on a particular cell? You need to learn how to attach code to Excel workbook or worksheet events!

Introduction to Handling Events in Excel VBA Code
Workbook Events (this blog)
Events for a Particular Worksheet
Bypassing Macros
Considerations for Other MS Office Application Events

This series of blogs is part of our Excel VBA online tutorial. If you want to learn more, have a look at our classroom-based courses in VBA macros and/or Excel.

Posted by Andy Brown on 22 November 2011

Workbook Events

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.

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

Displaying 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

Now that we've seen some events for workbooks, let's consider events at the worksheet level.

Parts of this blog
Introduction to Handling Events in Excel VBA Code Workbook Events (this blog) Events for a Particular Worksheet Bypassing Macros Considerations for Other MS Office Application Events

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR