Prevent Save As in a Excel Work Book using VBA Macro

If you want to prevent a workbook from saving you can do this simply by making it a read only file. To do this check the “Read-only recommended” check box in the File->Save options. But with this a user can save the file with different name using the Save As.. option.
If you want to stop Save As.. option too, you can easily do this using VBA macro.
Excel VBA has some inbuilt event. BeforeSave is such a event that occurs just before saving a file. We will use this event here to prevent Save as.. option.

First make sure you have saved your document as a Excel Macro-Enabled Workbook. Because after adding this code you wont be able to save your document as Excel Macro-Enabled Workbook. again.

macro enable workbook
Go to the Visual Basic Editor available in the developer tab.
visual basic editor in excel 2010
The double click on the ThisWorkbook object located on the left side.
prevent saving in excel vba macro
Now add this private module there.

Private Sub workbook_BeforeSave(ByVal NoSaveAs As Boolean, Cancel As Boolean)
Dim NoSaveReply As Long
If NoSaveAs = True Then
NoSaveReply = MsgBox("Sorry, you are not allowed to save this document with other name", vbQuestion + vbOKCancel)
Cancel = (NoSaveReply = vbCancel)
If Cancel = False Then Me.Save
Cancel = True
End If
End Sub

Save your document and close the Visual Basic editor.
Click on Save As.. option to see the result.

2 Comments

Add a Comment

Your email address will not be published. Required fields are marked *

Notify me of followup comments via e-mail. You can also subscribe without commenting.