Note: The other languages of the website are Google-translated. Back to English

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.


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

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!
officetab bottom
Comments (24)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
this doesn't work. I can't save the code it's gone once I reopen the file. How do I save the code?
This comment was minimized by the moderator on the site
Dear tee,
Thank you for your question. The article is updated to support saving the code into workbook now.
This comment was minimized by the moderator on the site
dear
the article is not updated. it doesnt work
This comment was minimized by the moderator on the site
Hi jen,
The article has been updated again with the problem solving. Please have a try. Thank you for your comment.
This comment was minimized by the moderator on the site
The update to the code adds a sheet, but never actually checks whether there is a TRUE/FALSE value in that sheet. The update fixes nothing, it just creates a useless sheet within the workbook.

I think you forgot to do a check whether there is a TRUE/FALSE value on that sheet.
This comment was minimized by the moderator on the site
As TEE posted, how do we save the file with the macro in? It is not there when I reopen. I am also now getting a bug on the ThisWorkbookSaved = True bit.
This comment was minimized by the moderator on the site
Dear Les,
Thank you for your question. The article is updated to support saving the code into workbook now.
This comment was minimized by the moderator on the site
As TEE and LES posted, how do we save the file with the macro in?
This comment was minimized by the moderator on the site
Hi,
Thank you for your question. The article is updated to support saving the code into workbook now.
This comment was minimized by the moderator on the site
Just to help whoever else reads this article looking for help with suppressing the save button I thought I'd place the answer down here as "Crystal" doesn't seem to be answering the question....


If you place the workbook in "Design Mode" and then hit SAVE it should work.
This comment was minimized by the moderator on the site
Dear Rogue,
I miss the point of the problem. Thank you so much for your help and sharing.

Best Regards
This comment was minimized by the moderator on the site
I keep getting debug error
This comment was minimized by the moderator on the site
Good day,
Which Excel version do you use?
This comment was minimized by the moderator on the site
Hello I am a fan of this website...I used to try the code. It works flawlessly. But it disables "Save option" too. I am not able to save the Excel sheet. What I need is to disable only the feature "Save As" I read the other user's comments too. ThisWorkbookSaved = True is not working for me as well. Could you please help? Thanks a lot for all your contributions.
This comment was minimized by the moderator on the site
Please, I do not want someone to copy or save my excel file, how can I remove save or save as?
This comment was minimized by the moderator on the site
I tried to use the code for NOT SAVING the file. But it did not work. It saves the file.
This comment was minimized by the moderator on the site
Thanks for your reply. Highly appreciated. I want you to guide me how to send a saved file to a friend who can open my file but only read or edit it, but can not not save the file in any folder.
1. For instance I have a saved file, which I want to send to "A" He opens the saved file but after opening he reads or makes any changes in my file, he can not save it at his end in any folder.
2. I have got code form internet which disallows to save. But in this case when I copy and paste the code, the file does not save. So I can not send the file to him
3. How can I send the file to Party "A" with a saved code in the hope he can not save the file.
This comment was minimized by the moderator on the site
Brilliant !   
This comment was minimized by the moderator on the site
For all of those who encountered the error where after already doing this once, when they try do it again the code won't save:

Go to the bottom of the excel file where it shows the sheets
Right-click the sheets
Press Unhide
Delete the sheet called CancelBeforeSave
This comment was minimized by the moderator on the site
Hi, Thanks very much, this is exactly what I need, to prevent others from saving or saving as a file in any location, and it is working for me, thanks to the comment about "Design Mode".

However, I still need to be able to make edits to the file periodically and save the changes. Must I delete the VBA code first, make the changes, Save, and then add the VBA code back in again to prevent others from saving? Or is there a simpler way to make edits and save while leaving the VBA code intact?
This comment was minimized by the moderator on the site
Hi Rochelley,
Thank you for your feedback. The VBA code has been updated as follow. You need to:
1. Press the Alt + F11 keys to open the Visual Basic editor.
2. In the Visual Basic editor, double click ThisWorkbook in the left Project pane, and then copy the following VBA code into ThisWorkbook (Code) window.
3. Save the code and save the workbook as an Excel Macro-Enabled Workbook.
Notes:
1) In the code, "Win10x64Test" stands for the username in your operating system. Please change it to your own username.
2) After adding the code, you can edit the workbook and save it as usual. But if someone else gets this workbook, the Save and Save as options will not be avaliable.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Updated by Extendoffice 20220930
    ThisWorkbook.Saved = True
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim xName As String
xName = "Win10x64Test" 'The username in your operating system

If xName <> Environ("username") Then
Cancel = True
End If
End Sub
This comment was minimized by the moderator on the site
I've just discovered that as soon as I place a shortcut to this file in another location (where the others are going to access it) all my protections no longer work. The sheet is not protected (which I have previously set), the workbook is not protected (which I have also previously set) and the Save-Save As ability is now there, as if the VBA code were not there at all. If I go to the original file, it all still works.

What is it about the shortcut that is stripping all the protections away?
This comment was minimized by the moderator on the site
Hi Rochelley,
The VBA code cannot figure out which way the workbook is opened. So it can't stop others from changing the file by opening it through a shortcut.
Sorry I can't help to with this problem.
For the first issue you mentioned above, I need time to see if I can handle it.
This comment was minimized by the moderator on the site
It is possible that user receiving the file will disable the macros and controlwill not work. Please help understand.
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations