CODEWITHSUNDEEP
excelvba
Ting Ping
Ting Ping

Reputation: 1145

How to disable the save as prompt?

Error Message

        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.

enter image description here

Upvotes: 5

Views: 60192

Answers (2)

Jaycal
Jaycal

Reputation: 2087

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

Open up the 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 wanted to notify the user that the function was disabled

To disable both the Save and Save As functionalities, you would remove the if statement and cancel the Save operation, regardless of if 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 would only need to look for the occurrence where the user is saving, but the SaveAsUI is not being displayed (i.e. user is simply saving 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 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.Saved variable 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

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

Add the following code after your 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

Upvotes: 5

Santosh
Santosh

Reputation: 12353

Try below code

Its always good to explcilty refer the workbook rather than 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

Upvotes: 9

Related Questions