Disable "save" option on close.

[Guest]

I would like to disable the "Do you want to save changes..." message after
running a macro when exiting the Excel program. I have a macro that runs and
changes the cell in a varies sheets, but I do not want my end user to be able
to save the changes. I have setup formulas thought out the sheets and I do
not want to load them every time. When I exit out of the program it promotes
me to always save. Is there a way to disable this or maybe make my Msgbox
button just exit out of the program without saving changes when click on
“OK� Then on that note, when I want to make changes, will I still be able to
save my workbook and if so, how?

 

[Dave Peterson]

You could put this kind of code in the ThisWorkbook module:

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = True
MsgBox "You can't save this!"
End Sub

But when you want to save, you'll have to turn off events.

hit alt-f11 to get to the VBE
hit ctrl-g to see the immediate window
type this and hit enter
application.enableevents = false

then save your workbook.

And remember to togglet that setting back to True
application.enableevents = true

So that events are enabled again.

 

[Dave Peterson]

This shouldn't be in the _BeforeClose event.

It should be in the _BeforeSave event.

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
MsgBox "You can't save this!"
End Sub

Sorry about the typo.