Often when working with Excel files we want to introduce certain features which are supposed to be fired in the circumstance of certain events. Say we have a Excel worksheet that is time-sensitive, that needs to be updated based on some web-resource or database. Should we hope that the user will leverage our Refresh button. Fortunately Microsoft introduced Excel Workbook Events – events which are fired in very specific situation in reaction to user interaction. This

Navigate

Introduction

Excel VBA Events such as the VBA Open Workbook event are very useful in organizing your Workbooks functionality and ease of use. Although VBA events have many advantages, be aware of one significant setback – whenever a VBA Event is fired VBA Code is executed! So? Isn’t that the point? Of course but that has some repercussions – as Excel (and other MS Office Applications) delete the Undo/Redo stack of events once VBA code is executed. This means that whenever a VBA Macro is executed the user will not be able to undo any previous changes to the Workbook using i.e. CTRL+Z.

Therefore be sure to limit your VBA Event handlers to the bare minimum as you may find users annoyed instead of impressed with your Excel GUI.

Workbook VBA Events

The VBA Open Workbook and other Workbook events are fired whenever an event in scope of the entire Workbook happens. This can include Activating, Saving, Opening or Closing the Workbook.

Adding Workbook Events

Let’s learn how to add Workbook events:

Select the Workbook module

Select the Workbook module from your Project – VBAProject window as shown below.

Select Workbook from the Object list

To view all available events associated with your Workbook select the Workbook value from the Object list in the Code window. This should automatically include the VBA Open Workbook event into your Workbook module.

Select the desired event from the available list of events

By selecting the Workbook Object from the previous list you will now have access to the available list of events associated with your Workbook. Simply select the desired VBA event from the list and the code will be added automatically.

List of Workbook Events (VBA Open Workbook etc.)

Below is a complete list of VBA Workbook Events available from Excel:

Worksheet VBA Events

Adding Worksheet Events

The VBA Open Workbook and other Workbook events are fired whenever an event in scope of the entire Workbook happens. This can include Activating, Saving, Opening or Closing the Workbook.

Adding Workbook Events

Let’s learn how to add Worksheet events:

Select a Worksheet module

Select the Worksheet module from your Project – VBAProject window as shown below. Be sure to select the Worksheet in which you want to add the event handler!:

Select Worksheet from the Object list

To view all available events associated with your Worksheet select the Worksheet value from the Object list in the Code window. This should automatically include the VBA SelectedChange Worksheet event into your Worksheet module.

Select the desired event from the available list of events

By selecting the Worksheet Object from the previous list you will now have access to the available list of events associated with your Worksheet. Simply select the desired VBA event from the list and the code will be added automatically.

List of Worksheet Events

Chart VBA Events

Adding Chart Events

Let’s learn how to add Chart events:

Select a Chart module

Select the Chart module from your Project – VBAProject window as shown below. Be sure to select the Chart to which you want to add the event handler!:

Select Chart from the Object list

To view all available events associated with your Chart select the Chart value from the Object list in the Code window. This should automatically include the VBA Activate Chart event into your Chart module.

Select the desired event from the available list of events

By selecting the Chart Object from the previous list you will now have access to the available list of events associated with your Chaet. Simply select the desired VBA event from the list and the code will be added automatically.

List of Chart Events

Use Cases

I decided to list some interesting examples of Workbook VBA events usage:

• Before save notification – if you don’t want to overwrite your current file version you may want to override the BeforeSave Workbook VBA event to add an additional prompt to confirm if you want to overwrite your current file. Code below:

  Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
      Cancel = (MsgBox("Are you sure?", vbYesNo, "Question") = vbNo)
  End Sub
  

• Refresh all PivotTables and QueryTables on VBA Open Workbook Event – if you want to make sure your Pivot Tables are up to date whenever someone opens your Workbooks add a simple piece of code:

  Private Sub Workbook_Open()
      ActiveWorkbook.RefreshAll
  End Sub

• Prevent Changes to certain Cells – say you want to prompt a user when changing a certain cell value and revert to a default value otherwise. Simply override the VBA Change Event as follows:

  Private Sub Worksheet_Change(ByVal Target As Range)
      If Target = Range("A1") And Target.Value <> 100 Then
         If MsgBox("Are you sure you want to change this cell?", vbYesNo) = vbNo Then
              'Revert Target value
              Application.EnableEvents = False
              Target.Value = 100
              Application.EnableEvents = True
          End If
      End If
  End Sub