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.

Was this article helpful?

0 0

Post a comment