VBA: BeforeClose + SaveChanges := False

  • Thread starter Thread starter Daniel Black
  • Start date Start date
D

Daniel Black

Hi there,

I have a macro in tied to the BeforeClose event, as noted below:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Do you want to save and make a backup?" ' Define message.
Style = vbYesNo ' Define buttons.
Title = "MsgBox Demonstration" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
SaveWorkbookBackup ' Perform some action.
Else ' User chose No.
Me.Close savechanges:=False ' Perform some action.
End If

When a user closes the file, the messagebox pops up asking if they want
to save and make a backup (obviously). If they choose "Yes," we've no
issues. If, however, they choose "No" (or, literally, anything BUT
"Yes"), the code instructs Excel to close the workbook without saving
changes, yet that's not precisely what happens.

When a user selects "No," the "Me.Close" triggers another BeforeClose
event, throwing the same dialog box up. It seems that the
"savechanges:= false" isn't working...or is it? You see, if on this
second time through the user is consistent, and selects "No," the
workbook closes. The logical result, unless something changes between
iterative executions, is that either Excel should close the file the
first go-round; or Excel should be stuck in an endless loop. Since
neither is actually the result, I have to think there's something
slightly different between each iteration.

Is it the fact that the second run through is a Close event motivated
by VBA, and therefore doesn't trigger anything tied to that event?
That seems to be the case, but short of creating a custom "Close File"
button for the user, I don't see a way around this.

Can anyone help me suppress this repetitive behavior so that, no matter
how the user closes the file, he need only respond once?

Thanks,

Daniel

End Sub
 
My guess is that your "Me.Close" is causing the 2nd BeforeClose event to be
triggered.

You don't need this (it is redundant but I don't know why you don't go into
an endless loop). Excel is on the verge of closing the workbook and will do
so *unless* you set Cancel = True within BeforeClose so you don't need to
close it yourself.

However, assuming that you don't want the user to see Excel's built-in "do
you want to save changes?" prompt, you should replace Me.Close with the
following:
Me.Saved = True
That will make Excel think that no changes have been made to the workbook
since the last save so it won't ask the user about saving and will simply
proceed with the close-in-process.

HTH,
 
Wonderful observation. I had taken the code from, I believe, something
from Mr. Walkenbach, and honestly hadn't considered the very simple
idea that they were already closing the workbook.

Thank you.

Daniel
 
Back
Top