VBA supports many events for Worksheet such as can capture operations when editing / deleting data, when Clicking, moving Sheet, deleting Sheet … And based on these events when executed, you can nest into paragraphs. VBA command to further customize their own tasks.
Events when working with WorkSheets in VBA
In VBA, you can quickly view and create events by selecting Worksheet and opening them in the event list. Note that:
- If you want to capture events on Sheet1, then in VBA you have to create events in Sheet1.
- Events between sheets will not affect each other.
1) Worksheet_SelectionChange
The event to execute when changing to select Cells or Range. For example, while in Cells A1, you switch to Cells A2, the event will be executed. The event works even when moving with the mouse or with the navigation keys. Note: Target: variable declared as Range. When dragging select 1 Range, Target will take the first Cell position. For example: Locate Cells in Row A when Clicked
1 2 3 4 5 6 | Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("A:A")) Is Nothing Then MsgBox "Hang " & Target.Column & " Cot " & Target.Row End If End Sub |
2) Worksheet_Activate
This event is fired when you move between Sheets of a Workbook. For example, while in Sheet1 you move through Sheet2, the event declared in Sheet2 will execute.
1 2 3 | Private Sub Worksheet_Activate() End Sub |
3) Worksheet_Deactivate
Unlike the event in Worksheet_Activate, the event declared in Sheet1 will execute.
1 2 3 | Private Sub Worksheet_Deactivate() End Sub |
4) Worksheet_BeforeDoubleClick
If you notice, then when Clicking on 1 Cells, they will have to Double Click on the Cells to be able to edit. So this event will be fired before the mouse pointer moves over the Cells. The event will not execute when 1 Click on Cells and use the keyboard to enter.
1 2 3 | Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) End Sub |
Note: Target: variable declared as Range. Cancel: Boolean variables will have a value of TRUE / FALSE. The value is FALSE when the event executes. For example: Write the current time when Double Click on Cells is in row A
1 2 3 4 5 6 7 8 | Private Sub Worksheet_BeforedoubleClick(ByVal Target As Range, Cancel As Boolean) If Not Intersect(Target, Range("A:A")) Is Nothing Then If Target.Value = "" Then Target.Value = Now() End If End If End Sub |
5) Worksheet_BeforeRightClick
Events executed when you Right-click on the worksheet. Will execute before Excel’s Context Menu is displayed.
1 2 3 | Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) End Sub |
Note: Target: variable declared as Range. Cancel: the Boolean variable will be TRUE / FALSE. The value is FALSE when the event executes.
6) Worksheet_Calculate
Events that execute when the operations on the Sheet are performed or refreshed For example, when you perform an addition operation, this event will execute. Execute only when calculations are performed on the spreadsheet.
1 2 3 | Private Sub Worksheet_Calculate() End Sub |
7) Worksheet_Change
When the editing finishes in the spreadsheet, this event will be fired. Including changes when entering text and changes from the results of functions.
1 2 3 | Private Sub Worksheet_Change(ByVal Target As Range) End Sub |