vba events

VBA Open Workbook and other VBA events

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

linkNavigate
Workbook VBA Events Worksheet VBA Events Chart VBA Events VBA Events Use Cases

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.
VBA Events
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.
Workbook Events: Select Workbook module

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.
Workbook Events: Select Workbook from Object list

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.
Workbook Events: Select Workbook Event

List of Workbook Events (VBA Open Workbook etc.)

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

Event Name Descriptions
ActivateEvent Workbook is activated
AddinInstall Workbook is installed as an add-in
AddinUninstall Workbook is uninstalled as an add-in
AfterSave Workbook is saved
AfterXmlExport Excel saves or exports data from the workbook to an XML data file
AfterXmlImport An existing XML data connection is refreshed or after new XML data is imported into the workbook
BeforeClose Before the workbook closes. If the workbook has been changed, this event occurs before the user is asked to save changes
BeforePrint Before the workbook (or anything in it) is printed
BeforeSave Before the workbook is saved
BeforeXmlExport Before Excel saves or exports data from the workbook to an XML data file
BeforeXmlImport Before an existing XML data connection is refreshed or before new XML data is imported into the workbook
Deactivate Workbook is deactivated
New New workbook is created
NewChart New chart is created in the workbook
NewSheet New sheet is created in the workbook
Open Workbook is opened
PivotTableCloseConnection After a PivotTable report closes the connection to its data source
PivotTableOpenConnection After a PivotTable report opens the connection to its data source
RowsetComplete The user navigates through the recordset or invokes the rowset action on an OLAP PivotTable
SheetActivate When any sheet is activated.
SheetBeforeDoubleClick When any worksheet is double-clicked, before the default double-click action.
SheetBeforeRightClick When any worksheet is right-clicked, before the default right-click action.
SheetCalculate After any worksheet is recalculated or after any changed data is plotted on a chart
SheetChange When cells in any worksheet are changed by the user or by an external link.
SheetDeactivate When any sheet is deactivated
SheetFollowHyperlink When you click any hyperlink in a workbook
SheetPivotTableAfterValueChange After a cell or range of cells inside a PivotTable are edited or recalculated (for cells that contain formulas)
SheetPivotTableBeforeAllocateChanges Before changes are applied to a PivotTable
SheetPivotTableBeforeCommitChanges Before changes are committed against the OLAP data source for a PivotTable
SheetPivotTableBeforeDiscardChanges Before changes to a PivotTable are discarded
SheetPivotTableChangeSync After changes to a PivotTable
SheetPivotTableUpdate After the sheet of a PivotTable report has been updated
SheetSelectionChange When the selection changes on any worksheet. Does not occur if the selection is on a chart sheet
Shutdown When the workbook host item shuts down
Startup After the workbook is running and all the initialization code in the assembly has been run
SyncEvent When the local copy of a worksheet that is part of a Document Workspace is synchronized with the copy on the server
WindowActivate When any workbook window is activated
WindowDeactivate When any workbook window is deactivated
WindowResize When any workbook window is resized

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.

See also  Excel Scrape HTML Add-In now with HTML caching

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!:
Worksheet VBA Events: Select a Worksheet module

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.
Worksheet VBA Events: Select the Worksheet object

Select the desired event from the available list of events

Worksheet VBA Events: Select the desired Worksheet VBA Event

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

Event Name Descriptions
Activate When a workbook, worksheet, chart sheet, or embedded chart is activated.
BeforeDelete
BeforeDoubleClick When a worksheet is double-clicked, before the default double-click action.
BeforeRightClick When a worksheet is right-clicked, before the default right-click action.
Calculate After the worksheet is recalculated, for the Worksheet object.
Change When cells on the worksheet are changed by the user or by an external link.
Deactivate When the chart, worksheet, or workbook is deactivated.
FollowHyperlink When you click any hyperlink on a worksheet. For application- and workbook-level events, see theSheetFollowHyperlink event and SheetFollowHyperlink event.
LensGalleryRenderComplete When a callout gallery’s icons (dynamic & static) have completed rendering.
PivotTableAfterValueChange After a cell or range of cells inside a PivotTable are edited or recalculated (for cells that contain formulas).
PivotTableBeforeAllocateChanges Before changes are applied to a PivotTable.
PivotTableBeforeCommitChanges Before changes are committed against the OLAP data source for a PivotTable.
PivotTableBeforeDiscardChanges Before changes to a PivotTable are discarded.
PivotTableChangeSync After changes to a PivotTable.
PivotTableUpdate After a PivotTable report is updated on a worksheet.
SelectionChange When the selection changes on a worksheet.
TableUpdate After a Query table connected to the Data Model is updated on a worksheet.

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!:
Chart VBA Events: Select Chart module

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.
Chart VBA Events: Select Chart Object

Select the desired event from the available list of events

Chart VBA Events: Select Chart Event

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

Event Name Descriptions
Activate chart sheet, or embedded chart is activated.
BeforeDoubleClick when a chart element is double-clicked, before the default double-click action.
BeforeRightClick when a chart element is right-clicked, before the default right-click action.
Calculate after the chart plots new or changed data, for the Chart object.
Deactivate when the chart, worksheet, or workbook is deactivated.
MouseDown when a mouse button is pressed while the pointer is over a chart.
MouseMove when the position of the mouse pointer changes over a chart.
MouseUp when a mouse button is released while the pointer is over a chart.
Resize when the chart is resized.
Select when a chart element is selected.
SeriesChange when the user changes the value of a chart data point by clicking a bar in the chart and dragging the top edge up or down thus changing the value of the data point.

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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.