Workbook Events In Excel VBA. Excel Training VBA lesson 15

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:

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.

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:

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:

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.

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 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.

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.

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.

Excel Training VBA Lesson 15

Workbook Events In Excel VBA

Information Helpful? Why Not Donate >> Excel Training-Video Series

<< PREVIOUS LESSON |NEXT LESSON>> | BACK TO EXCEL VBA LEVEL1 TRAINING INDEX

<< PREVIOUS LESSON |NEXT LESSON>> | BACK TO EXCEL VBA LEVEL1 TRAINING INDEX