Tom’s Tutorials For Excel: Preventing a Workbook From SaveAs

Tom’s Tutorials For Excel: Preventing a Workbook From SaveAs

When you want to keep the name of your workbook the same, without the ability to save it as another name, here’s an example of how to kinda sorta accomplish that.

“Kinda sorta”? Three things in life are certain: death, taxes, and programming code that falls short of perfection. There is nothing VBA can do to stop someone from going into their computer’s directory such as Windows Explorer, and manually changing the workbook’s file name without opening it.

From the Excel interface, there are two common methods to SaveAs, seen in the next two pictures.

Hitting the F12 key on the keyboard.


Clicking the SaveAs item on the toolbar or File menu.


With the below code, using those common interface methods will produce this message:



To install the code, from your keyboard press Alt+F11 which takes you to the Visual Basic Editor. From there, press Ctrl+R to make sure the Project – VBAProject window is visible.

Find your workbook (in this example it is named Book1), right-click on ThisWorkbook and left-click to select View Codeas shown in the next picture.

Paste the below BeforeSave event procedure into the workbook module as shown.

When you are done, press Alt+Q to close the VBE and return to your worksheet. Save the workbook. Test the code by attempting to SaveAs the workbook, and you’ll see the Message Box that is pictured above.

As a precautionary measure, lock the VBE with a password if you don’t want other users to wander into the VBE and change the code.

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

Select Case True

Case SaveAsUI
MsgBox "Sorry, you may not ''Save as'' this workbook." _
& vbCrLf & vbCrLf & _
"You may only save it, keeping its original name.", _
vbExclamation, "''Save as'' not allowed."
Cancel = True

Case Else
Exit Sub

End Select
End Sub