Event in VBA

Tram Ho

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

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.

3) Worksheet_Deactivate

Unlike the event in Worksheet_Activate, the event declared in Sheet1 will execute.

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.

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

5) Worksheet_BeforeRightClick

Events executed when you Right-click on the worksheet. Will execute before Excel’s Context Menu is displayed.

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.

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.

Share the news now

Source : Viblo