Excel 2019

Workbook Activate and Deactivate Events

The Workbook_Activate and Workbook_Deactivate events occur when you activate or deactivate a particular workbook. For example, the Workbook_Activate event occurs when the workbook containing this event becomes the active workbook and Workbook_Deactivate event occurs when the workbook containing this event looses the focus.

Workbook_Activate Event Procedure

The Workbook_Activate event occurs when the workbook is activated. For example, when the workbook is opened, or when Excel has more than one workbook opened and the workbook is activated (switch between that workbook).

Note: This event will not occur when you activate the workbook by switching from another application.

Example: Workbook_Activate() Event Procedure

  1. Open a new workbook and save it as BrainBell.xlsm.
  2. Press Alt + F11 to open the Visual Basic Editor window
  3. In the Project Explorer window, double-click ThisWorkbook
  4. In the code window, type the Workbook_Activate event procedure:
Private Sub Workbook_Activate()
 MsgBox ThisWorkbook.Name
End Sub
Workbook_Activate Event Procedure
The example procedure displays the file name BrainBell.xlsm when you activate the workbook.

To check if the event procedure working or not, switch to the Excel window and open a new workbook. Now switch back to workbook containing the event procedure (in our example BrainBell.xlsm). Excel should display the name of the workbook.

Workbook_Deactivate Event Procedure

The Workbook_Deactivate event occurs when the workbook loses focus. For example, when you open another workbook or close the workbook containing deactivate event procedure. This event does not occur when you switch to a different application.

Example: Workbook_Deactivate() Event Procedure

  1. Open the BrainBell.xlsm workbook
  2. Press Alt + F11 to open the Visual Basic Editor window
  3. In the Project Explorer window, double-click ThisWorkbook
  4. In the code window, type the Workbook_Deactivate event procedure:
Private Sub Workbook_Deactivate()
 MsgBox ThisWorkbook.Name & " deactivated"
End Sub
Workbook_Deactivate Event Procedure
The example procedure displays “BrainBell.xlsm deactivated” message when you activate a different workbook.

To check if the event procedure working or not, switch to the Excel window and activate a different workbook. This action will trigger the Workbook_Deactivate event procedure which displays a message box with BrainBell.xlsm deactivated message:
Workbook Deactivate MsgBox

Advertisement:
Advertisement: