Excel VBA example: The Open event for a workbook
One of the most commonly used Excel VBA events is the Workbook Open event. Assume that you have a workbook that you use every day. The Workbook_Open procedure in this example is executed every time the workbook is opened. The procedure checks the day of the week; if it’s Friday, the code displays a reminder message for you.To create the Excel VBA procedure that is executed whenever the Workbook Open event occurs, follow these steps:
- Open the Excel workbook.
Any Excel workbook will do.
- Press Alt+F11 to activate the VBE.
- Locate the workbook in the Project window.
- Double-click the project name to display its items, if necessary.
- Double-click the ThisWorkbook item.
The VBE displays an empty Code window for the ThisWorkbook object.
- In the Code window, select Workbook from the Object (left) drop-down list.
The VBE enters the beginning and ending statements for a Workbook_Open procedure.
- Enter the following statements, so the complete event-procedure looks like this:
Private Sub Workbook_Open()
Dim Msg As String
If Weekday(Now) = 6 Then
Msg = "Today is Friday. Don't forget to "
Msg = Msg & "submit the TPS Report!"
MsgBox Msg
End If
End Sub
The Code window should look like this.
This event-handler procedure is executed when the workbook is opened.
Workbook_Open is executed automatically whenever the workbook is opened. It uses the VBA’s WeekDay function to determine the day of the week. If it’s Friday (day 6), a message box reminds the user to submit a report. If it’s not Friday, nothing happens.
If today isn’t Friday, you might have a hard time testing this procedure. You can just change the 6 to correspond to today's actual day number.
And of course, you can modify this procedure any way you like. For example, the following version displays a message every time the workbook is opened. This gets annoying after a while.
A Workbook_Open procedure can do almost anything. These event-handlers are often used for the following:
- Displaying welcome messages (such as in Frank's cool workbook)
- Opening other workbooks
- Activating a particular worksheet in the workbook
- Setting up custom shortcut menus
Private Sub Workbook_Open()
Dim Cnt As Long
Cnt = GetSetting("MyApp", "Settings", "Open", 0)
Cnt = Cnt + 1
SaveSetting "MyApp", "Settings", "Open", Cnt
MsgBox "This workbook has been opened " & Cnt & " times."
End Sub
Using a Workbook_Open event-handler to keep track of how many times a workbook has been opened.Excel VBA example: The BeforeClose event for a workbook
Here’s an example of the Excel VBA Workbook_BeforeClose event-handler procedure, which is executed automatically immediately before the workbook is closed. This procedure is located in the Code window for a ThisWorkbook object:Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Msg As String
Dim Ans As Long
Dim FName As String
Msg = "Would you like to make a backup of this file?"
Ans = MsgBox(Msg, vbYesNo)
If Ans = vbYes Then
FName = "F:\BACKUP\" & ThisWorkbook.Name
ThisWorkbook.SaveCopyAs FName
End If
End Sub
This routine uses a message box to ask the user whether he would like to make a backup copy of the workbook. If the answer is yes, the code uses the SaveCopyAs method to save a backup copy of the file on drive F. If you adapt this procedure for your own use, you need to change the drive and path.Excel programmers often use a Workbook_BeforeClose procedure to clean up after themselves. For example, if you use a Workbook_Open procedure to change some settings when you open a workbook (hiding the status bar, for example), it’s only appropriate that you return the settings to their original state when you close the workbook. You can perform this electronic housekeeping with a Workbook_BeforeClose procedure.
When using the Workbook_BeforeClose event, keep this in mind: If you close Excel and any open file has been changed since the last save, Excel shows its usual “Do you want to save your changes” message box. Clicking the Cancel button cancels the entire closing process. But the Workbook_BeforeClose event will have been executed anyway.
Excel VBA example: The BeforeSave event for a workbook
The BeforeSave event, as its name implies, is triggered before a workbook is saved. This event occurs when you choose File → Save or File → Save As.The following procedure, which is placed in the Code window for a ThisWorkbook object, demonstrates the BeforeSave event. The routine updates the value in a cell (cell A1 on Sheet1) every time the workbook is saved. In other words, cell A1 serves as a counter to keep track of the number of times the file was saved.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI _
As Boolean, Cancel As Boolean)
Dim Counter As Range
Set Counter = Sheets("Sheet1").Range("A1")
Counter.Value = Counter.Value + 1
End Sub
Notice that the Workbook_BeforeSave procedure has two arguments: SaveAsUI and Cancel. To demonstrate how these arguments work, examine the following macro, which is executed before the workbook is saved. This procedure attempts to prevent the user from saving the workbook with a different name. If the user chooses File → Save As, the SaveAsUI argument is True.When the code executes, it checks the SaveAsUI value. If this variable is True, the procedure displays a message and sets Cancel to True, which cancels the Save operation.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI _
As Boolean, Cancel As Boolean)
If SaveAsUI Then
MsgBox "You cannot save a copy of this workbook!"
Cancel = True
End If
End Sub
Note that this procedure won't really prevent anyone from saving a copy with a different name. If someone really wants to do it, he or she can just open the workbook with macros disabled. When macros are disabled, event-handler procedures are also disabled, which makes sense because they are, after all, macros.