VBA - Events

VBA, an event-driven programming can be triggered when you change a cell or range of cell values manually. Change event may make things easier, but you can very quickly end a page full of formatting. There are two kinds of events.

Worksheet Events

Workbook Events

Worksheet Events

Worksheet Events are triggered when there is a change in the worksheet. It is created by performing a right-cpck on the sheet tab and choosing view code , and later pasting the code.

The user can select each one of those worksheets and choose "WorkSheet" from the drop down to get the pst of all supported Worksheet events.

Following are the supported worksheet events that can be added by the user.

Private Sub Worksheet_Activate() 
Private Sub Worksheet_BeforeDoubleCpck(ByVal Target As Range, Cancel As Boolean)    
Private Sub Worksheet_BeforeRightCpck(ByVal Target As Range, Cancel As Boolean) 
Private Sub Worksheet_Calculate() 
Private Sub Worksheet_Change(ByVal Target As Range) 
Private Sub Worksheet_Deactivate() 
Private Sub Worksheet_FollowHyperpnk(ByVal Target As Hyperpnk) 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Example

Let us say, we just need to display a message before double cpck.

Private Sub Worksheet_BeforeDoubleCpck(ByVal Target As Range, Cancel As Boolean)
   MsgBox "Before Double Cpck"
End Sub

Output

Upon double-cpcking on any cell, the message box is displayed to the user as shown in the following screenshot.

Workbook Events

Workbook events are triggered when there is a change in the workbook on the whole. We can add the code for workbook events by selecting the ThisWorkbook and selecting workbook from the dropdown as shown in the following screenshot. Immediately Workbook_open sub procedure is displayed to the user as seen in the following screenshot.

Following are the supported Workbook events that can be added by the user.

Private Sub Workbook_AddinUninstall() 
Private Sub Workbook_BeforeClose(Cancel As Boolean) 
Private Sub Workbook_BeforePrint(Cancel As Boolean) 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 
Private Sub Workbook_Deactivate() 
Private Sub Workbook_NewSheet(ByVal Sh As Object) 
Private Sub Workbook_Open() 
Private Sub Workbook_SheetActivate(ByVal Sh As Object) 
Private Sub Workbook_SheetBeforeDoubleCpck(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) 
Private Sub Workbook_SheetBeforeRightCpck(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) 
Private Sub Workbook_SheetCalculate(ByVal Sh As Object) 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) 
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) 
Private Sub Workbook_SheetFollowHyperpnk(ByVal Sh As Object, ByVal Target As Hyperpnk) 
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) 
Private Sub Workbook_WindowActivate(ByVal Wn As Window) 
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window) 
Private Sub Workbook_WindowResize(ByVal Wn As Window)

Example

Let us say, we just need to display a message to the user that a new sheet is created successfully, whenever a new sheet is created.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
   MsgBox "New Sheet Created Successfully"
End Sub

Output

Upon creating a new excel sheet, a message is displayed to the user as shown in the following screenshot.