Categories
Excel

Prevent Saving a Workbook

You can specify that any workbook be saved as read-only by checking the “Read-only recommended” checkbox to prevent a user from saving any changes he might make to the file.
You also can prevent users from being able to save a copy of your workbook to another directory or folder with or without a different name.

  1. Making Workbook Read-Only
  2. Using VBA to Prevent Saving Changes to a Workbook
  3. Disable Save As…

Make a Workbook Read Only

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.

Method 1:

  1. Click File » Info
  2. Click Protect Workbook
  3. Choose Always Open Read-Only
Making Workbook Read-Only

Method 2:

You can specify that any workbook be saved as read-only by checking the “Read-only recommended” checkbox when saving it.

  1. Click File » Save
  2. Select a location where you want to save the file, the Save As dialog box displayed
  3. Click the Tools menu and choose General Options
  4. Check the Read-only recommended checkbox
  5. Click OK

Using VBA to prevent saving changes to a workbook

We use the Workbook_BeforeSave event to prevent saving our workbook. 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 method, be sure not to save your workbook first. Putting this code into place on an already saved workbook prevents your workbook from ever saving further changes.

  1. Create a new workbook.
  2. Make some changes to the workbook without saving it.
  3. Open VBE (Visual Basic Editor) by pressing
    • Alt+F11 for Windows.
    • Opt+F11 (or Fn+Opt+F11) for MAC.
  4. Write following code in the ThisWorkbook module:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 If Not SaveAsUI Then
  MsgBox "You cannot save changes"
  Cancel = True
 End If
End Sub
  1. Save the workbook.
  2. Make some changes to the workbook.
  3. Try to save the workbook again, VBA shows you the following box:

Disable Save As

Sometimes, 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.

Type the following code into the VBE (in ThisWorkbook 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

Give it a whirl. Select File » Save and your workbook will save as expected. Select File » Save As..., however, and you’ll be informed that you’re not allowed to save this workbook under any other filename.

For details, visit Understanding Workbook AfterSave and BeforeSave Events.

Understanding Workbooks and Worksheets: