Workbook_BeforeClose

Excel Workbook_BeforeClose 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_BeforeClose is an Event triggered when a you close a Workbook (Excel). For example, you can use Excel Workbook_BeforeClose Event to save workbook, or initialize value (restore the default value overridden by user). By default, Excel would ask if you would save file before close if you have made changes.

In that case, the Event Procedure is triggered before this message box pops up.

Syntax of Excel Workbook_BeforeClose Event

Private Sub Workbook_BeforeClose( Cancel As Boolean)

Cancel – The default value of Cancel is False, which means the the workbook will close as normal after Event Procedure is triggered. Set to True if you don’t want to close the workbook, and no message box will prompt for saving file.

There is another Event called Workbook_Deactivate, which is triggered when a workbook is closed or switching to another workbook. 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_BeforeClose Event

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

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

Insert your code between Private Sub and End Sub. In the below example, when you close the workbook, a message box will prompt to confirm quitting Workbook or not. If “OK”,  the workbook will save and close. If “Cancel”, the workbook will not close.

Private Sub Workbook_BeforeClose(cancel As Boolean)
    x = MsgBox("Are you sure you want to quit?", vbOKCancel)
    If x = vbOK Then
        ThisWorkbook.Save
    Else
        cancel = True
    End If
End Sub

Outbound References

https://msdn.microsoft.com/en-us/library/office/aa220801%28v=office.11%29.aspx

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