Disabling the Save

[cstimart]

Long story short, would like to disable any/all saving functionality from a file only AFTER the macro has been run. Is that possible?

 

[cstimart]

I was able to create the functionality by using a hidden code w/in the spreadsheet...however, is it possible to use a function/variable?

 

[Scott Huish]

This will only work on a per instance basis. In other words if you close and reopen the spreadsheet it will reset.

In a standard module, use a Boolean public variable.

Like so:

Public MacroRunFlag As Boolean

Sub test()
MsgBox "Hello"
MacroRunFlag = True
End Sub

In the Workbook BeforeSave event:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = MacroRunFlag
End Sub

 

[cstimart]

Awesome!

Is it then possible to eliminate the "Do you want to save the changes...." prompt when closing the file?

 

[Scott Huish]

Change the Workbook BeforeSave event code to:

Private Sub Workbook_Open()
Cancel = MacroRunFlag
Saved = True
End Sub

 

[cstimart]

Change the Workbook BeforeSave event code to:

Private Sub Workbook_Open()
Cancel = MacroRunFlag
Saved = True
End Sub

Can you explain that one?

Also, I must be doing something wrong, as I still get the prompt.

 

[Scott Huish]

You didn't. I did, sorry. It should be the BeforeSave event, not the Open event:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = MacroRunFlag
Saved = True
End Sub

From the help file:

Excel Developer Reference
Workbook.Saved Property
True if no changes have been made to the specified workbook since it was last saved. Read/write Boolean.
Syntax

expression.Saved

expression A variable that represents a Workbook object.

Remarks

If a workbook has never been saved, its Path property returns an empty string ("").

You can set this property to True if you want to close a modified workbook without either saving it or being prompted to save it.

 

[cstimart]

I now have....

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If MacroRunFlag = True Then
    MsgBox "File will not save after running the Macro."
    Cancel = MacroRunFlag
    Saved = True
End If
End Sub

...and I still get the prompt.

 

[Scott Huish]

Do you still have the MacroRunFlag as a public variable in your module.

Have you set this to TRUE at the end of your macro?

 

[cstimart]

Do you still have the MacroRunFlag as a public variable in your module.

Have you set this to TRUE at the end of your macro?

Yes. It will not allow me to save the file after the macro is run if I manually try to save it. However, if I just close the file, it still prompts me to save the changes. (but does not allow any saving).