Excel

Workbook AfterSave and BeforeSave Events

In this tutorial we’ll learn about the AfterSave and BeforeSave events. The AfterSave event occurs after the workbook is saved and it can be use to ensures that the file is saved successfully or not. The BeforeSave event occurs before the workbook is saved, you can use this event to cancel the save operation if the specific conditions not met.

Workbook_AfterSave

The AfterSave event occurs after the workbook is saved. This event has one parameter Success which returns True if the save operation was successful; otherwise, False.

In the following example, the Workbook_AfterSave event procedure displays a message if the workbook is saved successfully. Press Alt + F11 to open VBE and write the following code in ThisWorkbook code module:

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
 If Success Then
  MsgBox ActiveWorkbook.Name & " was saved successfully"
 End If
End Sub
VBE AfterSave Event

Workbook_BeforeSave

Following example display True message if a dialog box displayed when you try to save the workbook, for example Save As dialog box.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 MsgBox SaveAsUI
End Sub

This event occurs before the Excel workbook is saved. The Workbook_BeforeSave event procedure accepts two parameters SaveAsUI and Cancel:

SaveAsUI: The value is True if:

  • the file is not already saved and a Save this file dialog box is displayed
  • or if the file is read only and Save As dialog box is displayed
  • or the Save As dialog box is displayed

Cancel: Default is False, if you set this argument to True, the workbook isn’t saved when the event procedure is finished.

Prevent Save As command

Create a new workbook, save it as beforeSave.xlsm , press Alt + F11 to open VBE and write the following code in ThisWorkbook code module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 If SaveAsUI Then
  MsgBox "You are not allowed to use Save As command"
  Cancel = True
 End If
End Sub
VBE BeforeSave Event

If SaveAsUI is True, its means the Save As dialog box is about to open, setting True to Cancel argument prevent the Save As dialog box from opening.

When a user tries to save the workbook, the Workbook_BeforeSave event procedure is executed. If the user used the Save As command, the SaveAsUI argument is True. In our example, the Workbook_BeforeSave checks SaveAsUI, if it is True, the procedure displays a message and sets the Cancel argument to True so the Save As dialog box won’t be shown.