What is Worksheet Events in excel VBA?
These are the types of events that would trigger based on the actions taken in the worksheet.
Examples of worksheet events
- Changing a cell in the worksheet
- Changing the selection
- Double-clicking on a cell
- Right-clicking on a cell
| Event Name | What triggers the event |
| Activate | When the worksheet is activated |
| BeforeDelete | Before the worksheet is deleted |
| BeforeDoubleClick | Before the worksheet is double-clicked |
| BeforeRightClick | Before the worksheet is right-clicked |
| Calculate | Before the worksheet is calculated or recalculated |
| Change | When the cells in the worksheet are changed |
| Deactivate | When the worksheet is deactivated |
| PivotTableUpdate | When the Pivot Table in the worksheet is updated |
| SelectionChange | When the selection on the worksheet is changed |
1) Worksheet Activate event:-
Occurs when a workbook, worksheet, chart sheet, or embedded chart is activated.
Examples 1
The below code unprotects a sheet as soon as it is activated.
Private Sub Worksheet_Activate()
ActiveSheet.Unprotect
End Sub
Example 2
We can use this event to make sure a specific cell or a range of cells (or a named range) is selected as soon as you activate the worksheet. The below code would select cell K1 as soon as you activate the sheet.
Private Sub Worksheet_Activate()
ActiveSheet.Range(“K1”).Select
End Sub
2) Worksheet Selection change events:-
The selection change event is triggered whenever there is a selection change in the worksheet.
This example of this event is when you want to highlight the active row and column of the selected cell.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
With ActiveCell
.EntireRow.Interior.Color = RGB(20, 123, 73)
.EntireColumn.Interior.Color = RGB(177, 98, 125)
End With
End Sub
The code first removes the background color from all the cells and then apply the one mentioned in the code to the active row and column.