Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
beforeclose cancel not working
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
beforeclose cancel not working
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. -- Regards, OssieMac " wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
beforeclose cancel not working
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. "OssieMac" wrote: 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. -- Regards, OssieMac " wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
beforeclose cancel not working
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 "Mike" wrote: 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. "OssieMac" wrote: 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. -- Regards, OssieMac " wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sub Workbook_Open and BeforeClose not working | Excel Programming | |||
Workbook_BeforeClose - Cancel not working | Excel Programming | |||
BeforeDoubleClick Cancel=True not working | Excel Programming | |||
Cancel Macro is user selects 'cancel' at save menu | Excel Programming | |||
Close workbook with "Cancel=TRUE" in the BeforeClose()" | Excel Programming |