Disable save controls

[rjplante]

I have a workbook file that I have saved with the word "Template" in the title. I have a set of steps the user completes, and then a save macro that will change the name and save the file in the intended file location. I would like to disable all save controls until the save macro runs and then have the last step of the save macro be to call the macro that restores all the save controls. This would be the Ctrl+S, File menu Save and Save As, F12 key, and ribbon and quick access icons. Is there a way to disable all of these functions using the Workbook_Open macro, and then what is the code to restore them all? I would also like to have a message box appear notifying the user that the file cannot be saved unless the save button is used.

 

[mumps]

Add an empty sheet and name it "Test". This sheet will use A1 as a helper cell. Place this macro in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up. Close the window to return to your sheet.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Sheets("Test").Range("A1") <> "Save" Then
        MsgBox "Saving of this file is not permitted until you have completed your steps."
        SaveAUI = False
        Cancel = True
    Else
        Sheets("Test").Range("A1").ClearContents
    End If
End Sub

Add this line of code at the end of your save macro:

Sheets("Test").Range("A1") = "Save"

 

[rjplante]

Awesome simple short code. Exactly what I was hoping it would be. Thanks for lending a hand.

 

[mumps]

You are very welcome.

 

[Mentor82]

Hi

Add an empty sheet and name it "Test". This sheet will use A1 as a helper cell. Place this macro in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up. Close the window to return to your sheet.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Sheets("Test").Range("A1") <> "Save" Then
        MsgBox "Saving of this file is not permitted until you have completed your steps."
        SaveAUI = False
        Cancel = True
    Else
        Sheets("Test").Range("A1").ClearContents
    End If
End Sub

Add this line of code at the end of your save macro:

Sheets("Test").Range("A1") = "Save"

Hi,
Nice work around buddy. I like it

 

[mumps]

Thank you for the feedback.

 

[rjplante]

I implemented the code you shared with me in my previous post and it works great to prevent the saving of the file before my save macro runs. However when I run my save macro now, I get a "Run-time error 1004: Method 'Save As' of object'_Workbook' failed, and the line of code that is flagged is listed below.

ActiveWorkbook.SaveAs Filename:=FSaveName, FileFormat:=52

I have added the following line of code to the top of the save macro to enable the save function.

'   Enables the 'Save' controls
Sheets("Entry").Range("AA15").Value = "Enabled"
SaveAUI = True
Application.ScreenUpdating = False

After my save macro fails, a file appears in the folder which looks like my file and has the correct file name. When I try to open it, it fails to open and says the following:

"Excel cannot open the file 'Job file 2700.xlsm' because the file format or the file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file."

Why is my save macro failing to complete the save procedure? I have a standalone macro with the two lines of code in the second code block above. Would it be better to list a call to that macro so that the entire macro runs and then is ended and then the rest of the save macro runs? How can I fix this?

Could it be an issue with the AUI command for Windows 10, or Excel 365 versus Excel 2016?

Lost and confused.

 

[mumps]

Could you upload a copy of your file, including any macros you are using, to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[rjplante]

The file can be found here.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Let me know if you cannot access it.

 

[mumps]

The link you provided does not take me to your file.