VBA Events
VBA, an event driven programming can be triggered when you change a cell or range of cells value manually. Change Event may make things easier, but you can very quickly end up 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 right-click on the sheettab and choosing 'view code', and then pasting the code.
User can select each one of those worksheets and choose "WorkSheet" from the drop down to get the list of all supported Worksheet events.
Below are the supported worksheet events that can be added by the user.
Private Sub Worksheet_Activate()
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Private Sub Worksheet_BeforeRightClick(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_FollowHyperlink(ByVal Target As Hyperlink)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Example
Let us say, we just need to display a message before double click.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
MsgBox "Before Double Click"
End Sub
Output
Upon double clicking on any cell, the message box is displayed to the user as shown below.
Workbook Events
Workbook events are triggered when there is a change to 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 below. Immediately Workbook_open sub procedure is displayed to the user as shown below.
Below 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_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Private Sub Workbook_SheetBeforeRightClick(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_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
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 succesfully 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 below.
