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.
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
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