Excel VBA Examples: Introductory Event Examples

If you are trying to get a good grasp on Excel VBA, you probably could benefit from a few examples to develop that visual basic prowess. Here, you find a few Excel VBA examples so that you can get the hang of this event-handling business.

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:

1. Open the Excel workbook.
   Any Excel workbook will do.
2. Press Alt+F11 to activate the VBE.
3. Locate the workbook in the Project window.
4. Double-click the project name to display its items, if necessary.
5. Double-click the ThisWorkbook item.
   The VBE displays an empty Code window for the ThisWorkbook object.
6. 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.
7. 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.

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

Here's a final Excel VBA example of a Workbook_Open procedure that uses the GetSetting and SaveSetting functions to keep track of how many times the workbook has been opened. The SaveSetting function writes a value to the Windows registry, and the GetSetting function retrieves that value (see the Help system for details). The followingExcel VBA example retrieves the count from the registry, increments it, and then saves it back to the registry. It also tells the user the value of Cnt that corresponds to the number of times the workbook has been opened.

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

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.