跳到主要內容

如何在Excel中禁用或不允許“另存為”選項？

通常，當我們單擊“保存”或“另存為”功能時，在工作簿中修改的數據將立即保存。 但是，有時，其他用戶在讀取Excel文件時不允許他們對其進行修改和保存。 本文提供了VBA方法來禁用Excel中的“另存為”和“另存為”選項。

使用VBA代碼禁用“另存為”選項

使用VBA代碼禁用“另存為”選項

您可以運行下面的VBA代碼以禁用Excel中的“另存為”和“另存為”選項。

1.在工作簿中，您需要禁用“保存和另存為”功能，請按 其他 + F11 同時打開 Microsoft Visual Basic for Applications 窗口。

2。 在裡面 Microsoft Visual Basic for Applications 窗口，雙擊 的ThisWorkbook 在左欄中，將下面的VBA 1複製並粘貼到“代碼”窗口中，然後單擊 節省 按鈕。 看截圖：

VBA 1：在Excel中禁用“另存為”選項

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ThisWorkbook.Saved = True
End Sub

3.在開幕 另存為 窗口，選擇一個文件夾來保存工作簿，根據需要命名工作簿，然後選擇 Excel啟用宏的工作簿 來自 保存類型 下拉列表，最後單擊 節省 按鈕。

4.現在，將下面的VBA 2複製並粘貼到 的ThisWorkbook 代碼窗口。 查看截圖。

VBA 2：在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。 點擊 節省 按鈕以保存代碼，然後關閉工作簿。

該工作簿已通過以下方式另存為Excel Macro-Enabled Workbook： 節省 和 另存為 功能已禁用。

備註：每次您修改和保存工作簿時，更改似乎都保存在當前工作表中。 但是在關閉並重新打開工作簿後，您會發現更改無法保存。

最佳辦公生產力工具

🤖 Kutools 人工智慧助手：基於以下內容徹底改變數據分析： 智慧執行   |  生成代碼  |  建立自訂公式  |  分析數據並產生圖表  |  呼叫 Kutools 函數...
熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup  |   多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧   |  工作簿和工作表管理器   |  資源庫 （自動文字）   |  日期選擇器   |  合併工作表   |  加密/解密單元格   |  按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 （過濾粗體/斜體/刪除線...）...
前 15 個工具集:  12 文本 工具 (添加文本, 刪除字符，...）   |   50+ 圖表 類型 (甘特圖，...）   |   40+ 實用 公式 (根據生日計算年齡，...）   |   19 插入 工具 (插入二維碼, 從路徑插入圖片，...）   |   12 轉化 工具 (數字到單詞, 貨幣兌換，...）   |   7 合併與拆分 工具 (高級合併行, 分裂細胞，...）   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能，體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

產品描述

Office選項卡為Office帶來了選項卡式界面，使您的工作更加輕鬆

  • 在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，Access，Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%，每天為您減少數百次鼠標點擊！
Comments (28)
No ratings yet. Be the first to rate!
Robert Crawford
Robert Crawford
This comment was minimized by the moderator on the site
When I try to save this in the code window, it shows an error message about Add, in the line:
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = xName & ""

how do I fix?
Reply
0
0
Deepak Kachare
Deepak Kachare
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.
Reply
0
0
crystal
crystal     Deepak Kachare
This comment was minimized by the moderator on the site
Hi Deepak Kachare,
If you don't want others users disable the macros after they receiving the file, you can hide the macro code that no one can open and see it.
See if the method in the following article can help you.
How to Protect and Hide Excel Macros
Reply
Report
0
0
DEEPAK KACHARE
DEEPAK KACHARE     crystal
This comment was minimized by the moderator on the site
Thanks for your response. I think the link is an erroneous one. Could you please help with the link.
Reply
0
0
crystal
crystal     DEEPAK KACHARE
This comment was minimized by the moderator on the site
Hi DEEPAK KACHARE,
Sorry for the mistake. To protect and hide Excel macros, you need to configure as follows.
1. Click the Alt + F11 keys to open the VBA editor.
2. In the editor, click Tools > VBAProject Properties.
https://www.extendoffice.com/images/stories/comments/comment-picture-zxm/vba3.png
3. In the VBAProject Properties dialog box, go to the Protection tab, check the Lock project for viewing box, enter password and finally click OK.
https://www.extendoffice.com/images/stories/comments/comment-picture-zxm/vba4.png
From now on, anyone (including yourself) who wants to open the VBA editor of this workbook, a password is required.
Reply
Report
0
0
Rochelley
Rochelley
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?
Reply
Report
0
0
crystal
crystal     Rochelley
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.
Reply
Report
0
0
Rochelley
Rochelley
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?
Reply
Report
0
0
crystal
crystal     Rochelley
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
Reply
Report
0
0
Ryan
Ryan
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
Reply
0
0
RJ
RJ
This comment was minimized by the moderator on the site
Brilliant !   
Reply
0
0
Satish
Satish
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.
Reply
0
0
Satish
Satish
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.
Reply
0
0
Joe
Joe
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?
Reply
0
0
Madhusudhan
Madhusudhan
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.
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations