The BeforeSaVe event for a Workbook

The BeforeSave event, as its name implies, is triggered before a workbook is saved. This event occurs when you use either the FileOSave or FileOSave As command.

The following procedure, which is placed in the Code window for a ThisWorkbook object, demonstrates the BeforeSave event. The routine updates the value in a cell (cell A1 on Sheet1) every time the workbook is saved. In other words, cell A1 serves as a counter to keep track of the number of times the file was saved.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI _ As Boolean, Cancel As Boolean) Sheets("Sheet1").Range("A1").Value = _ Sheets("Sheet1").Range("A1").Value +1

End Sub

Notice that the Workbook_BeforeSave procedure has two arguments, SaveAsUI and Cancel. To demonstrate how these arguments work, examine the following macro, which is executed before the workbook is saved. This procedure prevents the user from saving the workbook with a different name. If the user chooses the FileOSave As command, then the SaveAsUI argument is True.

When the code executes, it checks the SaveAsUI value. If this variable is True, the procedure displays a message and sets Cancel to True, which cancels the Save operation.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI _ As Boolean, Cancel As Boolean) If SaveAsUI Then

MsgBox "You cannot save a copy of this workbook!" Cancel = True End If End Sub

0 0

Post a comment