VBA MODULE 9


Events


Events are actions (mouse clicks, key presses) performed by users which trigger Excel VBA to execute code.

VBA is an event-driven programming language which can be triggered when a user makes any changes to the worksheet or workbook. On that basis there are two kinds of events.

 

  1. Worksheet Events
  2. Workbook Events

 

Worksheet Events

When a user makes any change to a worksheet then a worksheet event occurs.

Below figure shows the worksheet event handlers which we can use to define what needs to be done when we perform a particular action on the worksheet

The user can select any sheet(sheet1 ,sheet2, sheet3) from the left pane and choose "WorkSheet" from the drop down to get the list of all supported Worksheet events which can be seen on the right of the below figure .

 

 

Example of BeforeDoubleClick event

Suppose, we just need to display a message before double click, so when a user double clicks on any cell the message box is displayed with “Hello” message.

 

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

MsgBox "Hello" 

End Sub 

 

 

 

RESULT

 

 

Workbook Events

When a user makes any change to a workbook then a workbook event occurs.

First click on ThisWorkbook on the left pane and select '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. On the right side of the figure you can see the list of events for workbook.

 

 

Example of Open Event

Let us say, we just need to display a message to the user whenever he opens a workbook. For this we will select the workbook OPEN event from the dropdown list shown in the above figure and just put a single line between the code  which is shown in the following code

 

Private Sub Workbook_Open()

MsgBox "Good Morning"

End Sub

 

 

Result:

 

 

© Snagoff. All right reserved.