Lesson 6 Excel VBA Workbook and Worksheet Events
Category: [General,Excel] Demo Available
Workbook Download
This is a zipped Excel Workbook to go with this lesson.
Workbook and Worksheet Events
As are probably aware you can have Excel run a macro by using a CommandButton, AutoShape, Shortcut key etc. Most of the time this is fine as you only want to run the macro when you instruct it to run. But there are times when you may like or want the macro to run whenever a particular Event happens. Let's say each time you open your Workbook you would like the current date inserted into a cell, or each time you activate a particular Worksheet you want all data on it to be cleared. With Excel we can do this and much more! Excel has what is known as Events. The two most common being either Workbook Events or Worksheet Events. The word Event in this context means something that occurs whenever a particular action takes place which effects the Workbook or Worksheet Object. This might be a Workbook opening or a Worksheet cell changing or a Workbook closing etc. In fact Excel 2000 has 20 Workbook Events and 8 Worksheet Events. These are:
Workbook Events
- Workbook_Activate
- Workbook_AddinInstall
- Workbook_AddinUninstall
- Workbook_BeforeClose
- Workbook_BeforePrint
- Workbook_BeforeSave
- Workbook_Deactivate
- Workbook_NewSheet
- Workbook_Open
- Workbook_SheetActivate
- Workbook_SheetBeforeDoubleClick
- Workbook_SheetBeforeRightClick
- Workbook_SheetCalculate
- Workbook_SheetChange
- Workbook_SheetDeactivate
- Workbook_SheetFollowHyperlink
- Workbook_SheetSelectionChange
- Workbook_WindowActivate
- Workbook_WindowDeactivate
- Workbook_WindowResize
Worksheet Events
- Worksheet_Activate
- Worksheet_BeforeDoubleClick
- Worksheet_BeforeRightClick
- Worksheet_Calculate
- Worksheet_Change
- Worksheet_Deactivate
- Worksheet_FollowHyperlink
- Worksheet_SelectionChange
As you can see Excel offers a very rich environment for full automation if we wish. As there are so many of these Events we look in detail at the most popular and arguably the most useful of them. We will start with the Workbook.
Workbook Events
The most popular Events for the Excel Workbook are:
- Private Sub Workbook_Open() Occurs when the workbook is opened
- Private Sub Workbook_BeforeClose(Cancel As Boolean) Occurs immediately before the workbook closes. If the workbook has been changed, this event occurs before the user is asked to save changes.
- Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean) Occurs immediately before the Workbook is saved.
- Private Sub Workbook_NewSheet(ByVal Sh As Object) Occurs when a new sheet is created in the workbook.
You may have noticed that ALL these Events start with the words "Private Sub". This means they are only available to the Workbook Object Module. This is always called "ThisWorkbook" in the "Project Explorer". You can double click "ThisWorkbook" to gain access to the Workbooks Module. If you are not in the VBE you can right click on the sheet picture, top left next to "File" and select "View Code". In Excel 97 the default will be:
Private Sub Workbook_Open()
End Sub
If you are using Excel 2000 + the default will be blank. You can still save yourself the problem of having to type out the Workbook Event you want by clicking the drop arrow on the "Object" box (top left of the Module) and selecting "Workbook". This will then default to:
Private Sub Workbook_Open()
End Sub
In both versions 97 and 2000 + all the Events for the Workbook are listed in the "Procedure" box (top right of the Module). Simply select the Event you want and it will be written for you.
Lets look at each one of these is turn and put them to use.
Workbook_Open()
This is fairly self-explanatory. The Event will fire whenever the Workbook that houses the code opens. It can be used to make any needed checks upon opening, or to inform the user of some information, or to make needed changes upon opening. In fact as with most Events, the only limit is ones own imagination. The Workbook_Open() is ALWAYS the very first Event to fire in both the Workbook Events and the Worksheet Events. The next one to fire will be the Workbook_Activate(). Some users will quite often get confused with these two Events. The Workbook_Activate() can fire any number of times while the Workbook is open, while the Workbook_Open() will only fire once. As an example let's say you have two Workbooks open at one time, Book1.xls and Book2.xls with Book1.xls housing some code in the Workbook_Activate(). You need to go to Book2.xls so you activate it by going to Window>Book2.xls or selecting it from the Task bar. You make the changes then Activate Book1.xls again, this time the code that is within the Workbook_Activate() will run (or Fire). I frequently use the Workbook_Activate() and Workbook_Deactivate() to show and hide a custom CommandBar that I have created for a particular Workbook. I would use the Workbook_Open() to create the CommandBar only. It is important to note that if the user decides NOT to enable macros NO Events will fire. You can also prevent Events from firing by opening the Workbook from within Excel while holding down the Shift key.
Ok, so lets now use the Workbook_Open() Event in a useful way. Imagine you have a Workbook that needs to remind the user when it is the first day of the Month so that he/she must rollover the needed data. You could do this by displaying a ”Message box” upon opening:
Private Sub Workbook_Open()
If Day(Date) = 1 Then
MsgBox "It is the " & Format(Date, "dd/mmmm/yyyy") _
& " today, so please rollover the data.", vbInformation
End If
End Sub
This is a nice and simple way to inform the user of needed changes.
Workbook_BeforeClose(Cancel As Boolean)
This Event is nearly the complete opposite of the Workbook_Open() Event. I say nearly because it has the word "Before" in it. This means the Event fires just before the Workbook is closed not once it is closed. You will also notice that this Event has the words "Cancel As Boolean" in brackets. This is because this Event (as do many) takes an argument, in this case "Cancel" and returns a Boolean (True or False). Let's say you click the X to close a Workbook or go to File>Close, if you have made any changes Excel will ask you if you wish to save any changes you have made. On the dialog box it displays three choices "Yes", "No" or "Cancel" it is this "Cancel" which returns either True or False to the "BeforeClose" Event. The reason this is there is so that if the user DOES click "Cancel" you can opt whether to run your "BeforeClose" code or not.
Let's again use this in a practical way to see what I mean. Sticking with the "Workbook_Open" code example, let's say you can tell whether the data has been rolled over by seeing if Column A contains no data at all. If has not been rolled over you can decide the course of action. But if they "Cancel" at the save changes stage you don't want to take any action.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim iReply As Integer
'If they have "Cancelled" at the Save _
Changes dialog then do no more.
If Cancel = True Then Exit Sub
'See if it's the first day of the Month and _
count All entries in column A.
If Day(Date) = 1 And WorksheetFunction.CountA _
(Sheets("sheet1").Columns(1)) = 0 Then
'Inform them of the rollover and let them decide _
whether to continue closing or not.
iReply = MsgBox("You have not rolled over the data." _
& "Do you still wish to close", vbYesNo)
'If they choose "No" then stop the Workbook _
from closing.
If iReply = vbNo Then Cancel = True
End If
End Sub
So the very first thing that will happen when they close is that they will be asked if they want to save changes. If they choose "Cancel" then this will be passed back to our code as "True". Our code will then simply Exit Sub and go no further. Exit Sub: Immediately exits the Sub procedure in which it appears.
Next our code checks that it is the first day of the Month and also if Column A contains any entries at all. If it does then the message box is displayed asking if they still want to close, if they select No (vbNo) we use the "Cancel" argument to stop the Workbook from closing by setting it to True and then Exit the Sub. Of course if they select "Yes" (vbYes) the Workbook would close as normal.
- The Date function will return the current system date.
- The Day function will return a whole number (Integer) between 1 and 31, inclusive, representing the day of the month
- .
Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
As with the "BeforeClose" event this Event is fired immediately before the Event, which in this case is Save. It also takes two arguments "SaveAsUi" and "Cancel" both of which take a Boolean as their Value. The "Cancel" argument is nearly the same as the "Cancel" in the BeforeClose Event with one difference, and that is we can only set the argument to True or False we cannot have the argument passed to our code. This is simply because there is no built in function to stop a Save once it has started.
The "SaveAsUi" can also be set to True of False and will decide whether the "SaveAs" dialog will be displayed. So in a nutshell if we set "Cancel" to True the Workbook will NOT save. If we set "SaveAsUi" to False the "SaveAs" dialog will NOT be displayed.
For this example let's assume we want to stop users from saving a copy of our Workbook as a different name. We know that they can only do this from within the Excel interface by going to File>Save As. So to stop this from happening and at the same time telling them so, we could use this code:
Private Sub Workbook_BeforeSave _
(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI <> False Then
MsgBox "Please do not save another copy of this file", vbCritical
Cancel = True
End If
End Sub
This code will fire immediately before the user Saves, so if they just click Save (not Save As) the code will NOT enter the If statement because the "SaveAsUi" will return False (it's default). If however, they decide to go to File>Save As, a Boolean Value of True will be passed back to "SaveAsUi" and so our code WILL enter the If statement and show our message box. As soon as they "OK" the message box the "Cancel" argument will be set to True and stop the Workbook from saving.
Private Sub Workbook_NewSheet(ByVal Sh As Object)
This Event is fired whenever a new sheet is added to the Workbook. Some of its uses can be used to stop a user from adding any more sheets, pre-formatting a sheet for them, or automatically naming it. Again you will notice that it has "ByVal Sh As Object" this is really declaring the new sheet which is added as an Object. You may be wondering why it uses an Object and not a Worksheet for its declaration, this is because the Event also fires whenever a Chart sheet is added. A Worsheet Object cannot refer to a Chart Object. To put this Event to use let's make it easy on our user by automatically naming the sheet the same name as the current Month. As you are no doubt aware Excel will not allow you to have two sheets of the same name so we will need to place in some code to ensure they do not already have a sheet for the current Month.
Private Sub Workbook_NewSheet(ByVal Sh As Object)
'Stop run time error if sheet exists
On Error Resume Next
'Name the new sheet the current month
Sh.Name = Format(Date, "mmmm")
'If a sheet with the same name does exist then _
the sheet will be called sheetx. _
This means we should delete the sheet.
If Sh.Name <> Format(Date, "mmmm") Then
'Prevent the Excels standard sheet deletion warning.
Application.DisplayAlerts = False
Sh.Delete 'Delete the sheet
'Turn warnings back on
Application.DisplayAlerts = True
'Tell them what has happened.
MsgBox "You already have a sheet for " & Format(Date, "mmmm")
End If
End Sub
This code will work exactly as I stated above. You will notice that I have used "On Error Resume Next", "Application.DisplayAlerts=False" and "Application.DisplayAlerts=True" The "On Error Resume Next" will prevent the Run time error which would occur if there is already a sheet of the same name, in fact it will stop any Run time errors for the remainder of the code. Then "Application.DisplayAlerts=False" is used to stop Excel default warning messages such as the one you get when you delete a Sheet. Setting it back to True is just putting it back to its default.
The four above Workbook Events can be used in many different ways for many different reasons. They are ideal for automating procedures that you want to run when a specific Event occurs.
Worksheet Events
Let's now look at some of the Worksheet Events. These are not a lot different from the Workbook Events except that while the Workbook Events can apply to all Worksheets the Worksheets Events only apply the the Worksheet which houses them.
The most popular Events for the Excel Worksheet are:
- Private Sub Worksheet_Change(ByVal Target As Excel.Range) Occurs when cells on the worksheet are changed by the user or by an external link
- Private Sub Worksheet_Calculate() Occurs after the worksheet is recalculated
- Private Sub Worksheet_BeforeRightClick(ByVal Target As Excel.Range, Cancel As Boolean) Occurs when an embedded chart or worksheet is right-clicked, before the default right-click action
- Private Sub Worksheet_Deactivate() Occurs when a Worksheet in deactivated.
As with the Workbook Events all these start with the words "Private Sub". Again this means they are only available to the Worksheet Object in which they are housed. These Worksheet Objects can be seen in the "Project Explorer" and have the same name as the Worksheet tab name. You can double click the Worksheet Object to gain access to the Worksheet Module. If you are not in the VBE you can right click on the sheet name tab and select "View Code". In Excel 97 the default will be:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
End Sub
If you are using Excel 2000 + the default will be blank. You can still save yourself the problem of having to type out the Worksheet Event you want by clicking the drop arrow on the "Object" box (top left of the Module) and selecting "Worksheet". This will then default to:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
End Sub
In the both versions 97 and 2000 + all the Events for the Worksheet are listed in the "Procedure" box (top right of the Module). Simply select the Event you want and it will be written for you.
Lets look at each one of these is turn and put them to use.
As the Workbook_Open is arguably the most popular Workbook Event the Worksheet_Change is the most popular Worksheet Event.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
This event is fired whenever a cell on a Worksheet changes. The exception to this is deleting a cell, the Event does not fire then. You can see that this Event takes an argument that is called "Target" and is parsed to the Event as a range. The Target is always the cell that triggered the Event. Let's say that every time a user types a number in cells A1:A10 we need to multiply it by itself.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rWatchRange As Range
On Error GoTo ResetEvents
'Set range variable to A1:A10
Set rWatchRange = Range("A1:A10")
'The cell they have changed (Target) _
is within A1:A10
If Not Application.Intersect _
(Target, rWatchRange) Is Nothing Then
'They entered a number
If IsNumeric(Target.Value) Then
'Prevent the Event from firing again _
when we multiply it(the Target)by itself.
Application.EnableEvents = False
'Multiply it by itself
Target.Value = Target.Value * Target.Value
End If
End If
ResetEvents:
Application.EnableEvents = True
End Sub
Let's now work our way through the code to see what it does.
On Error GoTo ResetEvents
This is put in to force Excel to go to the line of code directly below "ResetEvents" should any unexpected errors occur.
Set rWatchRange = Range("A1:A10")
This sets our range variable to the Range we are interested in.
If Not Application.Intersect(Target, rWatchRange) Is Nothing Then
The Intersect returns a Range object that represents the rectangular intersection of two or more ranges. We use this to find out whether the cell which triggered the Event (Target) is within A1:A10 (rWatchRange). If the Target is NOT within the range A1:A10 the Intersect Method would return Nothing. The default for a empty Range Object. The "Not" is used to reverse the If statement.
If IsNumeric(Target.Value) Then
This will return True if the Target contains a number.
Application.EnableEvents = False
This is very important when using Worksheet Events. If we did not include it we could end up with an endless Loop. This is because we are changing the Target cell which would in turn re-trigger the change event, which would then run our code again and so on…..
Target.Value = Target.Value * Target.Value
Once we finally reach here we can multiply the value of the Target by itself.
ResetEvents:
Application.EnableEvents = True
The "Application.EnableEvents=True" turns the Events back on so it will run the next time. The "ResetEvents:" is a sign post for our "On Error GoTo". If an unexpected error does occur after Events have been disabled it will at least enable them again.
Private Sub Worksheet_Calculate()
This Event is fairly straightforward, it fires immediately after the Worksheet calculates. If we had a Worksheet full of formulas that were recalculating frequently we could use this to check whether the recalculated value is a certain cell has reached a certain limit. If it has, we could display a message box telling them to perform some action.
Private Sub Worksheet_Calculate()
If IsNumeric(Range("A1")) Then
If Range("A1").Value >= 100 Then
MsgBox "Range A1 has reached is limit of 100", vbInformation
End If
End If
End Sub
As you can see the Worksheet_Calculate Event takes no arguments.
Private Sub Worksheet_BeforeRightClick (ByVal Target As Excel.Range, Cancel As Boolean)
This Event fires immediately before the default action of the Worksheet right mouse click. In the case of the Worksheet that default action is the shortcut popup menu that appears. This Event takes two arguments "Target" and "Cancel". The Target is the same as the Target for the Worksheet ie; it refers the active Range Object. The Cancel refers to the shortcut popup menu, setting it to False will prevent the popup menu from showing. I have used this Event in the past to simply prevent the popup menu from showing when I have hidden all Excels standard Command Bars and replaced them with a customized one. I have also used it to display my own custom popup menu. As this is probably the best use for this Event, let's use it as an example.
Private Sub Worksheet_BeforeRightClick _
(ByVal Target As Excel.Range, Cancel As Boolean)
Dim cBar As CommandBar
Dim cCon1 As CommandBarControl
Dim cCon2 As CommandBarControl
'Prevent the standard popup showing
Cancel = True
'We must delete the old one first
On Error Resume Next
Application.CommandBars("Ozgrid").Delete
'Add a CommandBar and set the CommandBar _
variable to a new popup
Set cBar = Application.CommandBars.Add _
(Name:="Ozgrid", Position:=msoBarPopup)
'Add a control and set our 1st CommandBarControl _
variable to a new control
Set cCon1 = cBar.Controls.Add
'Add some text and assign the Control
With cCon1
.Caption = "I'm a custom control"
.OnAction = "MyMacro"
End With
'Add a control and set our 2nd CommandBarControl _
variable to a new control
Set cCon2 = cBar.Controls.Add
'Add some text and assign the Control
With cCon2
.Caption = "So am I"
.Caption = "AnotherMacro"
End With
cBar.ShowPopup
End Sub
Don't worry too much if you cannot fully understand how the adding Popup works, I only use this as an example because I believe it is the most relevant example. Let's step through it.
Cancel = True
As I said above this setting the Cancel argument to True will stop the default popup.
On Error Resume Next
Application.CommandBars("Ozgrid").Delete
This part is very important! While the code would run ok the very first time we ran it, it would not run the next time or anytime after. This is because a CommandBar called "Ozgrid" would already exist and so cause a run time error. The "On Error Resume Next" will prevent a run time error the first time it tries to delete the Command Bar "Ozgrid", which wouldn't yet exist.
Set cBar = Application.CommandBars.Add _
(Name:="Ozgrid", Position:=msoBarPopup)
Here we are adding a Command Bar calling it "Ozgrid" and setting it's Position to "msoBarPopup". You must do this with a Popup. If it was a normal Command Bar we could Position it at the top of the screen with all other Command Bars. We then set our variable to the new Command Bar.
Set cCon1 = cBar.Controls.Add
We now add a Control to our new Command Bar and set it to a variable.
With cCon1
.Caption = "I'm a custom control"
.OnAction = "MyMacro"
End With
Now we use the Control Object (cCon) and add a Caption, then assign it to a macro called "Mymacro", this would reside in a normal Macro.
cBar.ShowPopup
This simply tells Excel to show our new Popup.
Private Sub Worksheet_Deactivate()
This Event occurs whenever the Worksheet is Deactivated. You could use this on worksheet to ensure that the sheet always remained hidden. The user would have to go to Window>Unhide to view the sheet.
Private Sub Worksheet_Deactivate()
Me.Visible = xlSheetHidden
End Sub
Note the use of the keyword "Me" here. As the code is in the Private Module of the Worksheet Object we can refer to the Worksheet with this. The same could be used in all Worksheet Events. If we were dealing with a Workbook Object Event "Me" would always refer to the Workbook itself.
Summary
So as you can see we can have any macro or code run whenever a particular Event occurs. This can be a big advantage to the programmer as we do not have to rely on the user to activate it. It can also make life easy for the user as it can do things for them automatically, even without them knowing.
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets