VBA MODULE 9
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.
- Worksheet Events
- Workbook 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)
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"