beforeclose cancel not working

[mikepaiero]

Hey,

I am having a problem with a workbook... I'm using excel 2003 sp3 on
WinXP version 2002 SP3.


I cannot cancel a workbook close event, as I believe I should, by
setting 'Cancel' to true. I have code something akin to this

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Me.Saved = False Then
ClearToSave
Select Case MsgBox("Do you want to save the changes you made
to '" & Me.Name & "'", vbYesNoCancel + vbExclamation, "Microsoft Excel!
ent")
Case vbYes
.....
Case vbNo
.....
Case vbCancel
UpdateMSC
Cancel = True
End Select
End If
End Sub



before I added the sendkeys, I also tried creating an class module and
putting similar code in the class events, etc, setting that up
correctly. Both versions of the code work flawlessly EXCEPT that I
get a second "Do you want to save Yes / No / Cancel" . If I have both
the workbook event and the class module running the same code, I can
even see in the class module code that cancel is now TRUE (assuming I
hit cancel with my first Y/N/C messagebox)


What is the problem? I've seen tonnes of old posts on this but can't
find the solution.

 

[OssieMac]

Your quote: "EXCEPT that I get a second "Do you want to save Yes / No / Cancel"

I think that your second message is the system default message. Change some
of your message in the code (Make some of it upper case) and I think you will
be able to see the difference. My testing indicates that the particular
default message cannot be suppressed with Application.DisplayAlerts = False.

I wonder why you want to duplicate the default message.

 

[Mike]

Because I want to do certain things with my workbook based on whether or not
somebody really wanted to close the workbook, or if they clicked 'close' by
accident.


Why else would the beforeclose event allow you (or at least, is supposed to
allow you) to change the cancel flag?


My issue is that the functionality that is described in the help manual and
on the MSDN site for the beforeclose event doesn't seem to work, ie. I set
Cancel = True in the beforeclose event, but the workbook will still unless I
click the 'cancel' button on the second (default system yes, but should be
overridden) dialog.

 

[gpmichal]

I'm having a the same problem, but with differenct code. I have Excel 2007
SP2 installed on my home computer and work computer (both XP SP2). When
using the same code on both computers it will work with the home computer but
not the work computer. Just to test it, I used the following, very basic
code in the ThisWorkbook Object:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = True
End Sub

I tried using "run Microsoft Office Diagnostics" and Repair Microsoft Office
but it didn't correct the issue. The only thing I can think of to try next
is to uninstall and then reinstall Excel.

What do you think?

GP