563 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls 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 ...
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!
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.
|
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.
Parts of this blog |
---|
Some other pages relevant to the above blogs include:
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 2023. All Rights Reserved.