How to disable or do not allow Save & Save As options in Excel?
Normally, when we click Save or Save As function, our modified data in the workbook will be saved at once. But, sometimes, your Excel file is not allowed to be modified and saved by other users when they read the file. This article provides VBA method to disable the Save & Save As options in Excel.
Disable Save & Save As options with VBA code
Disable Save & Save As options with VBA code
You can run the below VBA code to disable the Save & Save As options in Excel.
1. In the workbook you need to disable the Save & Save As functions, please press Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, double click ThisWorkbook in the left bar, copy and paste the below VBA 1 into the Code window, and then click the Save button. See screenshot:
VBA 1: disable the Save & Save As options in Excel
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
End Sub
3. In the opening Save As window, select a folder to save the workbook, name the workbook as you need and select Excel Macro-Enabled Workbook from the Save as type drop-down list, and finally click the Save button.
4. Now copy and paste the below VBA 2 into the ThisWorkbook code window. See screenshot.
VBA 2: disable the Save & Save As options in Excel
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim xName As String
xName = "CancelBeforeSave"
If Not Evaluate("=ISREF('" & xName & "'!A1)") Then
Sheets.Add(after:=Worksheets(Worksheets.count)).Name = xName & ""
Sheets(xName & "").Move after:=Worksheets(Worksheets.count)
Sheets(xName & "").Visible = False
Exit Sub
End If
Cancel = True
End Sub
5. Click the Save button to save the code, and then close the workbook.
The workbook has been saved as an Excel Macro-Enabled Workbook with the Save and Save As functions disabled.
Note: It seems that changes are saved in current worksheet every time you modify and save the workbook. But after closing and reopening the workbook, you will find the changes were failed to save.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
