Disable Save and SaveAs RRS feed

  • Question

  • Hi everybody

    I have developed a file but I don't want to allow the users to save or saveas. I have tested the following codes but the can saveas the file.

    Private Sub Workbook_Open()
        Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save As...").Enabled = False
        Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save").Enabled = False
    End Sub

    and the other one, which I have tested is:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Application.ThisWorkbook.Saved = True
    End Sub
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
       Cancel = True
    End Sub

    The Excel Version, which I use is Excel 2016.

    Could anybody help me?

    Regards Parisa

    Thursday, April 27, 2017 11:33 AM

All replies

  • After adding the code, you have to disable events while saving the workbook, otherwise you won't be able to save the code!

    In the Immediate window in the Visual Basic Editor (Ctrl+G), enter

    Application.EnableEvents = False

    and press Enter. You can then save the workbook. Afterwards, enable events again using

    Application.EnableEvents = True

    Keep in mind that users can still copy and rename the workbook in File Explorer, and if they know about Application.EnableEvents, they can circumvent your protection.

    Regards, Hans Vogelaar (

    Thursday, April 27, 2017 12:33 PM