vba 如何禁用另存为提示？

How to disable the save as prompt?

提问by Ting Ping

        If IsWorkbookOpen("CONTRACT\CONTRACTLIST_Cement.xlsx") Then
            x = 0
        Else
            Application.DisplayAlerts = False
            ActiveWorkbook.Close savechanges:=True
            Application.DisplayAlerts = True
        End If

Hi, despite using the above code, the save as prompt still occasionally appears and affect the program. Does anyone know how to stop it completely? The problem is that after I click save as, it will alert me that it was still open.

您好，虽然使用了上面的代码，但是偶尔还是会出现另存为的提示，影响程序。有谁知道如何完全阻止它？问题是当我点击另存为后，它会提醒我它仍然是打开的。

回答by Santosh

Try below code

试试下面的代码

Its always good to explcilty refer the workbook rather than ActiveWorkbook

explcilty 参考工作簿总是好的，而不是 ActiveWorkbook

Sub test()
   If IsWorkbookOpen("CONTRACT\CONTRACTLIST_Cement.xlsx") Then
            x = 0
    Else
        Application.DisplayAlerts = False
        ThisWorkbook.Save
        ThisWorkbook.Close False
        Application.DisplayAlerts = True
    End If

End Sub

回答by Jaycal

You can use Workbook_BeforeSaveEvent in the ThisWorkbookobject to capture the user selecting SaveAs (or using the keyboard shortcut), which would result in the Save As prompt being displayed and the SaveAsUIbeing set to true. If SaveAsUIis true, this means the user is trying to save the file as something else, so you can then cancel the save As operation.

您可以Workbook_BeforeSave在ThisWorkbook对象中使用Event来捕获用户选择 SaveAs（或使用键盘快捷键），这将导致显示 Save As 提示并将其SaveAsUI设置为 true。如果SaveAsUI为 true，则表示用户正在尝试将文件另存为其他内容，因此您可以取消另存为操作。

Open up the Visual Basic Window (Alt + F11) and put the following code in ThisWorkbook.

打开 Visual Basic 窗口 (Alt + F11) 并将以下代码放入ThisWorkbook.

Disable Save As

禁用另存为

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

    If (SaveAsUI = True) Then
        MsgBox "Sorry. I can't have you saving this file as something else."
        Cancel = True
    End If
End Sub

You can delete the MsgBoxline if you want; i put it there as an example if you wanted to notify the user that the function was disabled

MsgBox如果需要，您可以删除该行；如果您想通知用户该功能已禁用，我将其放在此处作为示例

To disable boththe Save and Save As functionalities, you would remove the ifstatement and cancel the Save operation, regardless of if the Save as prompt is displayed.

要禁用既保存和另存为功能，您可以删除if语句，并取消保存操作，不管作为提示显示保存的。

Disable Save and Save As

禁用另存为

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

    MsgBox "Sorry. I can't have you saving this file at all."
    Cancel = True
End Sub

If you just want to disable the Save operation, you would only need to look for the occurrence where the user is saving, but the SaveAsUI is notbeing displayed (i.e. user is simply saving the file).

如果你只是想禁用保存操作，你只需要寻找其中用户节省的发生，但在SaveAsUI没有显示（即用户只是保存文件）。

Disable Save

禁用保存

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

    If (SaveAsUI = False) Then
        MsgBox "Sorry. I can't have you saving any updates to this file."
        Cancel = True
    End If
End Sub

Finally, note that the user will still get a prompt to save if the user simply closes the file. The user won't be able to save and the file will close, but if you want the experience to be a bit cleaner, you'll need to make an additional change. When a user closes a file, Excel checks the ThisWorkbook.Savedvariable to see if the file has been saved. If it is false, it will prompt the user to Save the file. To prevent this as well, we can set this boolean to true without saving, thus "tricking" Excel into thinking the file has been saved

最后，请注意，如果用户只是关闭文件，用户仍会收到保存提示。用户将无法保存并且文件将关闭，但如果您希望体验更简洁，则需要进行额外的更改。当用户关闭文件时，Excel 会检查ThisWorkbook.Saved变量以查看文件是否已保存。如果为false，则会提示用户保存文件。为了防止这种情况，我们可以在不保存的情况下将此布尔值设置为 true，从而“欺骗”Excel 认为文件已保存

Disable Save and Save As, including after User attempts to close file

禁用保存和另存为，包括在用户尝试关闭文件之后

Add the following code after your Workbook_BeforeSavecode.

在您的Workbook_BeforeSave代码后添加以下代码。

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ActiveWorkbook.Saved = True
    MsgBox "Click OK to continue closing this file. Changes will not be saved."
End Sub