Preventing Save As in a Workbook

You can specify that any workbook be saved as read-only by choosing Office button → Save → Tools Button → General Options and enabling the "Readonly recommended" checkbox (pre-2007, File → Save As → Tools [Options on the Mac] → General options in the Save options dialog). Doing so can prevent a user from saving any changes he might make to the file, unless he saves it with a different name and/or in a different location.

Sometimes, however, you might want to prevent users from being able to save a copy of your workbook to another directory or folder with or without a different name. In other words, you want users to be able to save on top of the existing file and not save another copy elsewhere. This is particularly handy when more than one person is saving changes to a workbook because you do not end up with a number of different copies of the same workbook, saved with the same name in different folders.

The Before Save event you'll be using has existed since Excel 97. As its name suggests, this event occurs just before a workbook is saved, enabling you to catch the user before the fact, issue a warning, and stop Excel from saving.

Before trying this at home, be sure to save your workbook first. Putting this code into place without having saved will prevent your workbook from ever saving.

To insert the code, open your workbook and choose Developer → Visual Basic, then select View → Code, and double-click on ThisWorkbook in the Project Explorer (pre-2007, right-click the Excel icon immediately to the left of the File menu item on the worksheet menu bar, and select View Code.

You might have to enable the Developer tab (not standard in Excel 2007) by selecting Office button → Excel Options → Popular, checking the option "ShowDeveloper tab in the Ribbon" and clicking OK.

This shortcut isn't available on the Mac. You'll have to open the Visual Basic Editor (VBE) by pressing Option-F11, or by selecting Tools → Macro → Visual Basic Editor. Once you're there, Ctrl-click or right-click This Workbook in the Projects window.

Type the following code into the VBE, and press Alt/c-Q to get back to Excel proper, then save your workbook:

    Private Sub workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
     Cancel As Boolean)
    Dim lReply As Long
     If SaveAsUI = True Then
    lReply = MsgBox("Sorry, you are not allowed to save this " & _
     "workbook as another name. Do you wish to save this " & _
     "workbook?", vbQuestion + vbOKCancel)
     Cancel = (lReply = vbCancel)
     If Cancel = False Then Me.Save
     Cancel = True
     End If
    End Sub

Select Office button → Save (pre-2007, File → Save) and your workbook will save as expected. However, select Office button → Save As (pre-2007, File → Save As...) and you'll be informed that you're not allowed to save this workbook under any other filename, unless you've disabled macros.

Note that when you save a workbook in Excel 2007 and it contains either macros or code, you will be prompted to save your workbook as an Excel macro-enabled workbook (*.xlsm) and will be unable to save in the standard Excel file format (*.xlsx).

[Contents] [Next]

Preventing Save As in a Workbook

In this tutorial: