In VBA (Visual Basic for Applications), the Activate and Deactivate events are used to run code when a worksheet, workbook, or userform becomes active or inactive, respectively. Here’s how you can use them:
For a Worksheet:
To use the Activate and Deactivate events for a worksheet, you need to place the code in the worksheet’s module in Excel. Here’s how you do it:
1. Open Excel.
2. Press ALT + F11 to open the VBA editor.
3. In the Project Explorer, find the worksheet you want to work with and double-click it.
4. This will open the code module for the selected worksheet.
5. Enter the Activate and/or Deactivate event procedures as shown below:
Private Sub Worksheet_Activate()
' Code to be executed when this worksheet becomes active
MsgBox "This worksheet has been activated."
End Sub
Private Sub Worksheet_Deactivate()
' Code to be executed when another worksheet is selected
MsgBox "This worksheet has been deactivated."
End Sub
Now, when you activate this worksheet, the Worksheet_Activate event will trigger, and when you switch to a different worksheet, the Worksheet_Deactivate event will trigger.
For a Workbook:
To capture the Activate and Deactivate events for a workbook, you should place the code in the ThisWorkbook module:
1. In the VBA editor, find ThisWorkbook in the Project Explorer.
2. Double-click ThisWorkbook to open its code module.
3. Enter the Activate and/or Deactivate event procedures as follows:
Private Sub Workbook_Activate()
' Code to be executed when this workbook is activated
MsgBox "This workbook has been activated."
End Sub
Private Sub Workbook_Deactivate()
' Code to be executed when another workbook is activated
MsgBox "This workbook has been deactivated."
End Sub
With this, the Workbook_Activate event will run when this workbook is made active, and Workbook_Deactivate will run when you switch to another workbook.
For a UserForm:
To handle the Activate and Deactivate events of a UserForm, you will need to add code to the UserForm’s code module:
1. In the VBA editor, find your UserForm in the Project Explorer.
2. Double-click the UserForm to open its code module.
3. Enter the Activate and/or Deactivate event procedures like this:
Private Sub UserForm_Activate()
' Code to run when the UserForm is activated (shown)
MsgBox "The UserForm is now active."
End Sub
Private Sub UserForm_Deactivate()
' Code to run when the UserForm is deactivated (loses focus or is hidden)
MsgBox "The UserForm is now deactivated."
End Sub
Remember that UserForm Deactivate events are not triggered by closing the form but by losing focus (e.g., if opening another form or window).
Please note that for all these event handlers to work, the VBA project should be saved in a macro-enabled workbook format (.xlsm or .xlsb for Excel workbooks), and macros should be enabled in Excel’s security settings.
Unlock Your Potential
Excel
Access
Power BI
