How to disable saving as a hint?

Question

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, saving as a hint still sometimes appears and affects the program. Does anyone know how to stop it completely? The problem is that after I click "Save As", it will warn me that it is still open.

Answer 1

Try entering the code

Its always useful for explanation, referring to a book, not an 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 

Answer 2

You can use the Workbook_BeforeSave Event in the ThisWorkbook object to capture the user who selects SaveAs (or using the keyboard shortcut), which will cause the Save As prompt to be displayed, and the SaveAsUI parameter will be set to true. If SaveAsUI true, it means the user is trying to save the file as something else, so you can cancel the save operation as.

Open a Visual Basic window (Alt + F11) and put the following code in 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 MsgBox line if you want; I put it there as an example if you want to notify the user that the function has been disabled.

To disable the Save and Save As functionality, you must remove the if and cancel the save operation, regardless of whether the Save As prompt is displayed.

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 will only need to look for the case when the user saves, but SaveAsUI does not appear (i.e. the user just saves the file).

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 be prompted to save if the user simply closes the file. The user cannot save and the file closes, but if you want the experience to be a little cleaner, you need to make additional changes. When the user closes the file, Excel checks the ThisWorkbook.Saved variable to see if the file is saved. If it is false, it will prompt the user to save the file. To prevent this, we can set this boolean to true without saving, thus "tricking" Excel into thinking that the file was saved.

Disable saving and saving as, including after a user attempts to close the file

Add the following code after the Workbook_BeforeSave code.

 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