BeforeSave Example

To use a BeforeSave event, you must enter the code in ThisWorkbook (Microsoft Excel Objects)

This code will cause a message box to show each time you save - "Do you really want to save this Workbook?"

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   If SaveAsUI = False Then
      If MsgBox("Do you really want to save this Workbook? ", vbYesNo) = vbNo Then
         Cancel = True
         Exit Sub
      End If
   End If
End Sub

Note where it says "SaveAsUI". This is to determine whether the user has pushed "Save" or "Save As".

It works like this -

If the user has pushed "Save" (not "Save As" which is ignored by using "If SaveAsUI = False Then"), show a message box with the above question, else "Cancel" (don't save), and "Exit" the code.

Another thing to keep in mind using "SaveAsUI" limits the code to work with saved workbooks only.

This means that in the case of the above code, the message will not appear in the case that workbook is not saved yet (eg "Book1", "Book2" etc)

© Copyright All Rights Reserved.