VBA Events

Visual Basic for Applications (VBA) is a programming language used primarily for automating tasks in Microsoft Office applications, such as Excel, Word, and PowerPoint. Events in VBA allow you to respond to specific actions or occurrences within these applications. They enable you to automate tasks, enhance user interfaces, and create interactive solutions. In this introduction, we’ll explore the fundamental concepts of VBA events.

What are VBA Events?

VBA events are actions or occurrences that happen within an application, and you can write code (event handlers) to respond to these events. Events allow you to create interactive and dynamic applications by triggering specific actions or functions in response to user interactions or other system events.

VBA has a plethora of events like this for which you can write custom code. As soon as an event occurs, any code you have associated with it will be run immediately. When Excel detects an occurrence, it does this automatically. You need only create the necessary code and insert it into the proper event subroutine.

Types of VBA Events

There are six main types of VBA events:

1. Workbook Events

These events occur at the workbook level in Excel. Examples include opening or closing a workbook, saving it, or changing its structure. Common workbook events include Open, Close, BeforeSave, and SheetChange.

2. Worksheet Events

Worksheet events occur at the individual sheet level within a workbook. Examples include changes to cell values, selection changes, or the activation of a sheet. Common worksheet events include Change, SelectionChange, and BeforeDoubleClick.

3. Chart Events

Chart events are related to charts in Excel and can be used to trigger actions when users interact with a chart. It includes clicking on a data point or resizing a chart. The common chart events include MouseDown, BeforeDoubleClick, and DragOver.

4. UserForm Events

UserForms are custom dialog boxes or forms that you can create in Excel. Events related to UserForms allow you to control user interactions with the form elements, such as buttons, text boxes, and combo boxes. Some of the common UserForm events include Initialize, Click, and Change.

5. Application Events

Application-level events occur at the Microsoft Excel application level, rather than at the workbook or worksheet level. These events are not tied to a specific document or workbook and can be used for more global automation tasks. Common application events include WorkbookOpen, WorkbookBeforeClose, and NewWorkbook.

Workbook Level Events

Here is a list of most commonly used events in a workbook.

                  Name of the events                                    What triggers the event
Activate Activation of a workbook
AfterSave Installation of workbook as an add-in
BeforeSave The saving of a workbook
BeforeClose The closing of a workbook
BeforePrint The printing of a workbook
Deactivate Deactivation of a workbook
NewSheet Addition of a new sheet
Open Opening of a workbook
SheetActivate Activation of any new sheet in the workbook
SheetBeforeDelete Deletion of any sheet
SheetBeforeDoubleClick Double-click on any sheet
SheetBeforeRightClick Right-click on any sheet
SheetCalculate Calculation or recalculation of any sheet
SheetDeactivate Deactivation of a sheet
SheetPivotTableUpdate Update of workbook
SheetSelectionChange Change in a workbook
WindowActivate Activation of a workbook
WindowDeactivate Deactivation of a workbook

Common VBA Workbook Events

1. Workbook Open

Access to the workbook triggers the Workbook Open event. When an Excel workbook is opened, the user will be greeted with this notice. The event can be altered such that a prompt appears every time a user opens a worksheet.

In addition, it has the following applications:

  • Whenever the workbook is opened, show a greeting.
  • You need to keep track of when each person who accessed the worksheet did so.
  • When you want the next person to open the workbook to see a reminder.
  • When only certain days of the week should trigger a certain message upon workbook launch

2. Workbook BeforeSave Event

When an Excel user chooses to save the current workbook, an event known as Workbook BeforeSave is triggered. Two situations can set off the Workbook BeforeSave Event:

  • When you first save the workbook.
  • The workbook is already in a saved state.

3. Workbook BeforeClose Event

Workbook BeforeClose occurs before a workbook is closed. Whether or whether the workbook is open, the VBA code will be run. If the user is prompted to save the workbook, and then cancels, the workbook will not be saved even if the user had intended to save it. On the other hand, the event code will have already been activated because the BeforeClose Event has been triggered.

Worksheet Level Events

Worksheet events take place based on the triggers in the worksheet.

Read below the following frequently used events in a worksheet.

Name of the events  What triggers the event
Activate Activation of the worksheet
BeforeDelete Before the deletion of the worksheet
BeforeDoubleClick Before double click on the worksheet
BeforeRightClick Before right-clicking on the worksheet
Calculate Before the calculation of the worksheet
Change Change in the cells of the worksheet
Deactivate Deactivation of the worksheet
PivotTableUpdate Update of the Pivot Table in the worksheet
SelectionChange Change in the selection on the worksheet

Common Worksheet Events

1. Worksheet_Selectionchange

In the editor, choose the sheet for which you want to run event-based instructions, and then click Worksheet


When the user’s selection shifts, the SelectionChange event is automatically added:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub

Here is a code that highlights the currently selected cells in a spreadsheet and unhighlights them when the selection changes:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'The blank cells are Static previous Selection As String
'When will select different cell then background color will be changed for the selected cell

If previousSelection <> "" Then
Range(previousSelection).Interior.ColorIndex = xlColorIndexNone
End If

'Color the current selection
Target.Interior.Color = RGB(181, 200, 0)

'Save the address of the current selection
previousSelection = Target.Address

End Sub



2. Worksheet_Activate (On Sheet Activation)

When the sheet is activated, this event occurs:

Private Sub Worksheet_Activate()
End Sub

For example, when the sheet is active, choose cell A2:

Private Sub Worksheet_Activate()
Range("A2").Value = "Example of Worksheet Activate"
End Sub

3. Worksheet_Deactivate (On Sheet Deactivation)

When a different worksheet in the workbook is activated, this event occurs:

Private Sub Worksheet_Deactivate()
End Sub

For example, When you’re done with the sheet, delete the contents of rows A2 through A10.

Private Sub Worksheet_Deactivate()
End Sub

4. Worksheet_Beforedoubleclick (On Double-Click)

A double-click on a sheet cell will cause this to happen:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
End Sub

For instance, double-click a cell to make it green (or red):

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Double click on the cell if the color is Green it will change to red
If Target.Interior.Color = 5 Then
Target.Interior.ColorIndex = 3 'Green color
Target.Interior.Color = 5 'Red color
End If
End Sub

Setting the Cancel variable to True will stop the double-click event from happening.

5. Worksheet _Beforerightclick (ON Right-Click)

If you right-click on the sheet, the following will happen:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
End Sub

For example, if the clicked cell is in column C, insert the current date on right-click:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 1 Then
Target = Date
Cancel = True
End If
End Sub

If the Cancel variable is set to True, the right-click event will be cancelled (and the context menu will not appear).

6. Worksheet_Change (On Cell Change)

This event occurs whenever there is a change to the sheet’s cell content:

Private Sub Worksheet_Change(ByVal Target As Range)
End Sub


In a nutshell, VBA events are the secret sauce that transforms static software into dynamic, user-friendly marvels. Harnessing these events empowers developers to craft custom solutions, making work in Microsoft Office applications a breeze. Don’t miss out on the magic of VBA events – they are your ticket to automation excellence!

Leave a Comment

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

Scroll to Top