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.
File » Info
Always Open Read-Only
You can specify that any workbook be saved as read-only by checking the “
Read-only recommended” checkbox when saving it.
File » Save
- Select a location where you want to save the file, the
Save Asdialog box displayed
- Click the
Toolsmenu and choose
- Check the
- 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.
- Create a new workbook.
- Make some changes to the workbook without saving it.
- Open VBE (Visual Basic Editor) by pressing
Fn+Opt+F11) for MAC.
- Write following code in the
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
- Save the workbook.
- Make some changes to the workbook.
- 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
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.