Excel Workbook.Deactivate Event

Excel Workbook_Deactivate Event

Excel predefines some popular actions that  you would do on different Objects (worksheet, workbook, button, etc), those actions are called Event. For example, activating a worksheet is an Event, closing a workbook is an Event, clicking on a button is an event. Each Object has its own list of Events, Workbook has a list of Events (e.g. close workbook, open workbook), worksheet has a list of Events (e.g. activate worksheet, edit a Cell).

If you perform an Event, say, closing a workbook, your desired code can be triggered. For example, you may want to save a workbook automatically when you close a workbook, or you may want a welcome message box to pop up when a workbook is opened. Event is a Sub Procedure (begin with Private Sub and end with End Sub) and is generated automatically (see in the below section) with a specific name, you can call a Sub Procedure or write your own code within the Event code.

Excel Workbook_Deactivate is an Event triggered when a you deactivate a Workbook (Excel).  For example, you can use Excel Workbook_Deactivate Event to save workbook, or initialize value (restore the default value overridden by user).

To understand when to use Excel Workbook_Deactivate Event, you must understand the meaning of “activate”.  Have you ever used ALT+PrintScreen? Normal PrintsScreen button in keyboard can capture the whole screen in your monitor, but ALT+PrintScreen only capture the “active” Windows. Take a look at the below print screen.

assignMacro1

To capture the above screen, first I click on the Record Macro box (Windows), it floats on top of other applications, then press ALT+PrintScreen. To deactivate this Windows, just click on something else such as other application.

The Excel Workbook_Deactivate Event is triggered when

1) Switch from workbook containing Workbook_Dactivate Event to another workbook (will not trigger Event if you switch to other application such as MS Word)

2) Close a workbook

There is another Event called Workbook_BeforeClose, which is triggered when a workbook is closed. Both Workbook_BeforeClose and Workbook_Deactivate can be triggered when a workbook is closed, but if both of them are present in a workbook, Workbook_BeforeClose will trigger first.

Example of  Excel Workbook_Deactivate Event

1) Press ALT+11 to enter VBE (Visual Basic Editor)

2) Double click on ThisWorkbook > select Workbook > Select Deactivate

workbook_deactivate

Insert your code between Private Sub and End Sub. For example, the below code saves the workbook when deactivated.

Private Sub Workbook_Deactivate()
    ThisWorkbook.Save
End Sub

Outbound References

https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.deactivate.aspx

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *