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
George Nicholson wrote:
> 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,
> --
> George Nicholson
>
> Remove 'Junk' from return address.
>
>
> "Daniel Black" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >
|
Similar Threads
