Vba worksheet events in Excel
Just as Workbook has some events associated with it, so does a worksheet .
The Worksheet events occur on a specific action performed on a worsheet.
There are a number of events associated with the worksheet, some of which
are discussed below.
Worksheet Activate event
This event occurs when a worksheet is activated.
This event is helpful when some action has to happen while activating a sheet.
This scenario arises when the user has to be notified of wrong sheet selection.
The following code snippet illustrates this.
- Private Sub Worksheet_Activate()
- MsgBox "Sheet Q47 has been activated"
- End Sub
You might have seen a msgbox while entering into this sheet.its because of its activate event.
- Private Sub Worksheet_Change(ByVal Target As Range)
- If Target.Address = "$M$17" Then
- MsgBox "Worksheet change event occured"
- End If
- End Sub
Enter any value in Cell M17, the worksheet change event triggers.
WorkSheet Deactivate event
As the name implies, this event occurs, when the sheet is deactivated or left.
The following code illustrates this.
- Private Sub Worksheet_Deactivate()
- MsgBox "You are leaving this sheet Q47"
- End Sub
While leaving this sheet, a msgbox will pop up, meaning that, Deactivate event has triggered.
The screenshot of the above codes in the editor are as shown below:
There are lot more events associated with the Worksheet, which can be tried on your own as an exercise.