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
Output:
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").Select
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()
Range("A2:A10").ClearContents
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 Else 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
Conclusion
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!
