Disable/Enable Save and Save As

[mumps]

I am using a macro which saves a file automatically to the Desktop. What I would like to do is disable Save/Save As using VBA code immediately after the file is automatically saved to prevent it from being saved again. I would also like to disable the 'Save/Save As' window from opening when the file is closed. I have tried using the 'Before_Save' and 'Before_Close' events but the 'Before_Save' event prevents the macro from automatically saving the file. Could this be done using some lines of code in my macro rather than using the 'Before' events? I then need some code to enable 'Save/Save As' in another macro. Any suggestions would be greatly appreciated.

Also posted at: Disable/Enable Save and Save As

 

[Ron de Bruin]

If the file is saved ones can't you check out if it exist on the desktop or is the name not hard coded ?
You can stop the macro if it still exists

You can start here
Disable Command bars and Controls in Excel 97-2003

See also the shortcuts part and if you use 2007-2013 you need to do it with RibbonX
You can check out my Ribbon page

 

[Snakehips]

mumps,

Is this approach of any value?

You could do this with a retained global variable but here I am suggesting using a helper cell.
My code is for helper cell Sheet1 A1, you can edit this to be any cell, anywhere in your workbook, hidden or otherwise.

Get the code in your autosave macro use the following line (edit for your helper address), to set the helper cell to "AS" before it calls the save.

If Sheets("Sheet1").Range("A1") = "" Then  Sheets("Sheet1").Range("A1") = "AS"

That will allow one save only via that macro. (Unless it suits you to re-set to "" for any reason, elsewhere in your coding)

Use the following code in the before save event.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim MyCell As Range
Set MyCell = Sheets("Sheet1").Range("A1") 'edit sheet and range to suit***
Select Case MyCell
Case ""            'normal save to be allowed pre the disabling auto save???? if not then delete this line
Case "AS"         'AS denotes being autosaved by the disabling macro
MyCell = "DS"   'NS denotes disallow future saves
Case Else         'If DS or anything bar as 0r "" disable Save and Save As
SaveAUI = False
Cancel = True
End Select
End Sub

In your second macro, which is to be allowed to make a save, use the following line, with edit, before calling the save.

Sheets("Sheet1").Range("A1") = "AS"

I hope that helps.

 

[mumps]

Hello Ron and Snakehips. Thank you both for your quick responses.

This is the code that I am using to save the file on my Desktop:

Application.ScreenUpdating = False
    Application.Cursor = xlWait
    Application.DisplayAlerts = False
    
    Dim DesktopAddress As String
    DesktopAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop")
    ChDir DesktopAddress
    ActiveWorkbook.SaveAs Filename:=DesktopAddress & "\" & Sheets("MeetData").[A1] & "RegionalT&F" & " " & Replace(Sheets("Meetdata").[A2], "/", "-") & "Backup" & ".xlsm", FileFormat:=52
    
    Application.DisplayAlerts = True

Ron: I added the following lines immediately following this code:

Application.CommandBars("Worksheet Menu Bar").Enabled 	= False
Application.CommandBars("Standard").Enabled = False

However, after I ran the code, I was still allowed to Save or Save As. The corresponding buttons were not disabled. I'm obviously doing something wrong.

Snakehips: I also tried your suggestion and and encountered the same problem.

The sheet "MeetData" is protected so I included a line to unprotect it before the code was run. I simply (or so I thought) want to prevent the file from being saved again after it is saved by the code. Your advice is greatly appreciated.

 

[Ron de Bruin]

The code you try is not for 2007 and up

See file 7 in the download for examples that use RibbonX
Ribbon Examples files and Tips

But remember you must also disable the shortcuts with onkey
Disable key or key combination or run a macro if you use it

What you want to do is not easy because making a copy in explorer is also very easy.

If you only want to disable a second saveas in your macro it is not so difficult

Hello Ron and Snakehips. Thank you both for your quick responses.

This is the code that I am using to save the file on my Desktop:

Application.ScreenUpdating = False
    Application.Cursor = xlWait
    Application.DisplayAlerts = False
    
    Dim DesktopAddress As String
    DesktopAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop")
    ChDir DesktopAddress
    ActiveWorkbook.SaveAs Filename:=DesktopAddress & "\" & Sheets("MeetData").[A1] & "RegionalT&F" & " " & Replace(Sheets("Meetdata").[A2], "/", "-") & "Backup" & ".xlsm", FileFormat:=52
    
    Application.DisplayAlerts = True

Ron: I added the following lines immediately following this code:

Application.CommandBars("Worksheet Menu Bar").Enabled 	= False
Application.CommandBars("Standard").Enabled = False

However, after I ran the code, I was still allowed to Save or Save As. The corresponding buttons were not disabled. I'm obviously doing something wrong.

Snakehips: I also tried your suggestion and and encountered the same problem.

The sheet "MeetData" is protected so I included a line to unprotect it before the code was run. I simply (or so I thought) want to prevent the file from being saved again after it is saved by the code. Your advice is greatly appreciated.

 

[mumps]

Thank you so much once again, Ron. I opened file 7 in the download. This is the macro that I saw:

Option Explicit'Callback for Paste onAction
Sub MyPasteMacro(control As IRibbonControl, ByRef cancelDefault)
    MsgBox "This is my custom Paste button"
End Sub

I noticed that the 'Save As' button was dimmed and disabled. This is exactly what I would like but I would also like the 'Save' button to be dimmed and disabled. With the two buttons dimmed and disabled, will my macro still be able to save the file automatically or will this prevent my code from running? I am not sure how to apply this macro in my situation. Would you please be so patient as to guide me through the process? I also have the following line to disable the 'Save' shortcut:

Application.OnKey "^s", ""

and this line to disable the 'Save' popup window from appearing when the file is closed.

 ActiveWorkbook.Saved = True

Will these lines work in my code? I will then also need some code to enable the buttons. Perhaps I am asking for too much of your time. I will certainly understand if this is the case.

 

[Snakehips]

mumps,

I realise that my code does not actually dim and directly disable the save and save as and it is undoubtably, a crude approach compared to Ron's,
However, I believe it does prevent further saving of the file, either manually or by the original code.
If you should fail with the more sophisticated approach then you could perhaps add a message to mine that would inform the user that an attempted manual save did not take place?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim MyCell As Range
Set MyCell = Sheets("Sheet1").Range("A1")  'edit A1 to suit***
Select Case MyCell
Case "" 'normal saveto be allowed pre the disabling auto save????  if not then delete this line
Case "AS"  'AS denotes being autosaved by the disabling macro
MyCell = "DS" 'NS denotes disallow future saves
Case Else  'If DS or anything bar as 0r "" disable Save and Save As
MsgBox "Saving of this file by the user is not permitted"
SaveAUI = False
Cancel = True
End Select
End Sub

 

[Ron de Bruin]

You must edit the RibbonX in the file :delete the line that call the Paste code and add your Save disable line
Your code will still work because it only disable the buttons.
When you deactivate or close your file it will automatic Enable the buttons.

Open the file in the UI Editor, if you are new to this start here
Change the Ribbon in Excel 2007 - 2013
Note: see that the you must add the RibbonX in two places in the Editor (2007 and 2010-2013 section)

In 97-2003 it is a lot easier as you can see.
When I have time this evening I will post the RibbonX for you

But remember that if a user disable macros it still can use F12 or Ctrl-s to SaveAs and Save

Thank you so much once again, Ron. I opened file 7 in the download. This is the macro that I saw:

Option Explicit'Callback for Paste onAction
Sub MyPasteMacro(control As IRibbonControl, ByRef cancelDefault)
    MsgBox "This is my custom Paste button"
End Sub

I noticed that the 'Save As' button was dimmed and disabled. This is exactly what I would like but I would also like the 'Save' button to be dimmed and disabled. With the two buttons dimmed and disabled, will my macro still be able to save the file automatically or will this prevent my code from running? I am not sure how to apply this macro in my situation. Would you please be so patient as to guide me through the process? I also have the following line to disable the 'Save' shortcut:

Application.OnKey "^s", ""

and this line to disable the 'Save' popup window from appearing when the file is closed.

 ActiveWorkbook.Saved = True

Will these lines work in my code? I will then also need some code to enable the buttons. Perhaps I am asking for too much of your time. I will certainly understand if this is the case.

 

[mumps]

Thank you, Ron. I will wait for your post.

Snakehips: I tried your code again and it works perfectly. There is only one problem. When I run my macro, the file is saved automatically which is what it is supposed to do. The problem is that somehow your 'Workbook_BeforeSave' macro gets deleted from 'ThisWorkbook' and consequently, it isn't available to prevent another save. After it was deleted, I manually pasted it back in and then the 'Save,Save As' was disabled. If we can determine what is deleting the macro in 'ThisWorkbook', then everything works well. There is nothing that I know of in my code that would delete any other code so I don't know what could be causing this problem. Would you or perhaps Ron have any ideas?

 

[Snakehips]

mumps,

??????? Seems an odd one to me !!!!! Given that we are trying to limit saving, did you ensure that after first copying the code into 'ThisWorkbook' that you actually saved the file, manually, with no restriction???
IE if you have retained the 'Case "" ' line in the code then save with the helper cell blank.
or If you have deleted that line or made it a remark then do a manual save with the helper containing "AS"

Let me know how it goes.