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