The Before Close event for a workbook

Here's an example of the Workbook_BeforeClose event-handler procedure, which is automatically executed immediately before the workbook is closed. This procedure is located in the Code window for a ThisWorkbook object:


Sub Workbook_BeforeClose(Cancel As Boolean)


Msg As String


Ans As Integer


FName As String


= "Would you like to make a backup of this file?"


= MsgBox(Msg, vbYesNo)

If Ans = vbYes Then

FName = "F:\BACKUP\" & ThisWorkbook.Name

ThisWorkbook.SaveCopyAs FName



End Sub

This routine uses a message box to ask the user whether he would like to make a backup copy of the workbook. If the answer is yes, the code uses the SaveCopyAs method to save a backup copy of the file on drive F. If you adapt this procedure for your own use, you probably need to change the drive and path.

Excel programmers often use a Workbook_BeforeClose procedure to clean up after themselves. For example, if you use a Workbook_Open procedure to change some settings when you open a workbook (hiding the status bar, for example), it's only appropriate that you return the settings to their original state when you close the workbook. You can perform this electronic housekeeping with a Workbook_BeforeClose procedure.

oj^NG/ There is a caveat with the Workbook_BeforeClose event. If you close Excel and any open file has been changed since the last save, Excel will show its usual "Do you want to save changes..." message box. Clicking the Cancel button cancels the entire closing process. But the Workbook_BeforeClose event will have been executed anyway.

0 0


  • anna
    When i save a shared workbook with filter on I get file is locked?
    8 years ago

Post a comment