Excel 2007 BeforeClose(Cancel as Boolean) doesn't cancel


  • I have set up similar event handlers in ThisWorkbook and AppEventClass and properly linked them to the events so that I can reach a break statement in each (using each individually and both together in different tests). For example:

    Private Sub Appl_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
        a = MsgBox("Got Here", vbYesNo)      ' Msgbox appeared and accepted Yes and No buttons on different trials.
        If a = vbNo Then                              ' Debug break here was reached, 'a' controls the If as expected
            Cancel = True
            Cancel = False
        End If
    End Sub

    (I don't normally allow implict variables or obscure names, but this was from a sample in MSDN.)

    Whether I respond with VbNo or not, Excel proceeds to the standard exit handler for unsaved files.

    (Do you want to .... Yes, No, Cancel)

    If I select Yes or No, Excel 2007 exits (with or without a save)

    If I select Cancel, Excel 2007 does what I expected it to do when I set Cancel=True in the event handler.

    I tried explicit 'ByRef Cancel as Boolean' and removing the Private designation, separately and together, with no effect.

    I am (almost) confident that the Cancel worked as I expected in Excel 2003. 

    I suspect that I am up against some sort of paranoia (security) setting,

            but I have found no guidance on where it might be set.

    Searching MSDN has lead to nothing that I could see as useful.

    The internet search re-affirmed my opinion that the code should work as shown, even when 2007 was the explicit product.

    (It also turned up numerous interesting ideas like testing the value passed in Cancel -

    everything I have seen and tried indicated that it should come as False.)

    Wednesday, March 02, 2011 9:06 PM

All replies

  • Assuming the WithEvents class is instanciated and Appl refers to the Application, your code as posted should trap the BeforeClose event of each and any workbook. If Cancel is returned as False the workbook will not close, and neither will the application (if say the workbook is closing because user is closing the app).

    It's unusual to trap in that way at app level for the close event of each and every workbook, normally trap at workbook level of the relevant workbook. You can do that within the Thisworkbook module of the workbook or in another project using WithEvents

    Public WithEvents pWB As Excel.Workbook

    In passing you can declare the variable to return your msgbox result like this (assuming VBA)
    Dim a As VbMsgBoxResult
    you could also declare as Long

    Try again, with the event in the ThisWorkbook module, then at both workbook and application levels. Complete the event stubs from the dropdown, don't copy and paste.

    Peter Thornton

    Thursday, March 03, 2011 11:25 AM
  • To repeat:

    " I have set up similar event handlers in ThisWorkbook and AppEventClass and properly linked them to the events so that I can reach a break statement in each (using each individually and both together in different tests)."

    I regret that my choice to include the example from the Application level rather than the one from ThisWorkbook was confusing. I started out using ThisWorkbook because I already had some BeforeClose cleanup. I tried the Application level when ThisWorkbook didn't respond to setting the value of Cancel. 

    From the documentation, it appears that ThisWorkbook.BeforeClose(Canel as Boolean) and Application.WorkBookClose(WB as Workbook, Cancel as Boolean) should have the same result, and in fact they do - neither cancels the close. (As an aside, when I used both  - true desperation - ThisWorkbook ran first.)

    At both levels, the message box is displayed and I also use debug halts to step through looking for misoperation, so I am confident that they are being invoked and exiting normally.

    The operation after each is the same whether I set True or False for Cancel.

    The documentation indicates to me that Cancel=True should prevent the Close - as for the Cancel button on the Excel default save on exit message. But at a minimum I would expect to see some difference in the operation after End Sub based on the value set for Cancel.

    What else, probably outside this code, is required in order to make one or the other approach actually respond to the value set for Cancel? 

    Thursday, March 03, 2011 2:43 PM
  • Is your code normal VBA, in an Excel workbook. If not be sure that with whatever language you are using ByRef is default. If in your language ByVal is default that would explain. I doubt that is the reason though because if you had used the dropdown to complete the stub it would have been correctly filled.

    As you can break it seems your events are firing, so .EnableEvents is not the issue. I'm out of ideas! I can only repeat what I suggested before, start again in a new session and workbook, taking the event stub from the dropdown (don't copy/paste). Work in Excel VBA (if you're not already). It really should work as documented, always does for me!

    Peter Thornton

    Thursday, March 03, 2011 3:47 PM
  • To repeat:

    ·         I tried explicit 'ByRef Cancel as Boolean' and removing the Private designation, separately and together, with no effect."



    ·           Yes it is standard Excel .xlsm VBA and I did create the stub from the drop down.

    When you say it works for you, are you using Excel 2007 12.0.6545.5000 from the Office Professional Plus Suite. I am (almost) completely confident that similar code gave me no issues in Excel 2003.

    (The stub in ThisWorkbook was created in Excel 2003, but I did not need to Cancel then. The one at Application level was created in 2007, so the root is not version conversion.)

    I am very suspicious that the origin is somewhere in the paranoia settings, in part  because the reason for adding Cancel here is that, only reported since the last ‘critical security patch’ from MS, the default save message ‘Yes’ option will sometimes save a file that cannot be re-opened.

    (At re-opening, the file cycles through ‘Excel has to close’ – ‘Excel is repairing the file’ – ‘Excel has to close’  - … until Task Manager crunches the copy of Excel. If you do a save file then exit with No the file can be re-opened and Cancel on the default save screen works perfectly. If the file is re-opened with Macros disabled for all sources, the file can be opened to recover the data. If the data is pasted into a fresh copy of the .xlsm, the macros will work fine and the file can be saved and re-opened.)

    I can’t guarantee that there weren’t unreported cases before the last security patch, but these users don’t tend to be shy about reporting any possible (or impossible) issue.

    Thursday, March 03, 2011 6:21 PM
  • The code should work in the exact same way in ALL versions form 97 to 2010. And yes, my code has been used in numerous 2007 setups. Further, if for some reason the method had been deprecated I would have heard about it!

    So, clearly there is something odd or wrong for in your set up (or your user's).  I don't know what but you have now introduced all sorts of new info that suggests something else is going on causing problems and crashes, although I don't understand all your terminology.  You say you suspect the problems have only occurred since some security update. Over the years one or two updates have caused problems but I very much doubt that's the issue here. However, to be sure, why not restore to the re-update state.

    But before doing that, have you done what I suggested, already twice and now again for the third time, namely, write the test code again from scratch in 2007 completing the stubs from the dropdown. Start again in a new instance and an otherwise clean workbook, and in a setup that is otherwise working normally. It really should work in any version of Excel.

    Peter Thornton

    Thursday, March 03, 2011 6:55 PM
  • IT installs the updates and doesn't provide rollback as an option.

    Proving to them that they created a problem and should consider fixing it is on the order of using a lever to move the Earth. 

    The code fragment that I originally provided was created from the pulldown stub based on an article in MSDN (which is why I didn't declare the data type for a, I think it defaults to variant, but I wasn't sure)

    first in VBA for Excel in the copy of Excel where I encountered the problem,

    then in a new copy of Excel (close Excel and with no other data or macros that the one that I was testing - I even deleted the default Open stub.

    I also created the ThisWorkbook.BeforeClose version in the clean workbook. I did not create them in separate test workbooks. I did not reboot between the original and test workbooks. I did create the ThisWorkbook.BeforeClose code on a second system on our network with identical results. (Excel.exe is on each C:, not on the network, so it really was an independent executable, but subject to the same security updates.)

    As far as 'otherwise working normally' I can only say that I don't see any other symptoms that would show up in this copy. Specifically, I have not seen the corruption on Save Yes with the test file, but I don't know what other conditions might be involved in causing that (and was trying to avoid having to go that route or at least gain more controlled insight into what is happening when.)

    I was hoping that the issue might be related to some obscure macro security setting that the users still have authority to change.  I guess that was a vain hope. 

    Thursday, March 03, 2011 9:05 PM
  • Proving to them that they created a problem and should consider fixing it is on the order of using a lever to move the Earth.

    Unless its a very recent update from MS that's the culprit the issue would have had caused plenty of complaints by now, with the update cited. IOW, doubtful it's the update.

    The code fragment that I originally provided was created from the pulldown stub based on an article in MSDN (which is why I didn't declare the data type for a, I think it defaults to variant, but I wasn't sure)

    Undeclared variables are always Variants, irrelevant for this issue. However it's always worth completing the event stubs yourself rather than relying on copy/paste.

    I was hoping that the issue might be related to some obscure macro security setting that the users still have authority to change. I guess that was a vain hope.

    If it was a macro security issue the code wouldn't work at all, however the fact you can put a break in the event proves it's not that.

    There's obviously an explanation but I'm out of ideas without being able to see your setup. Like I say, it really should work (you'll have to trust me on that!).  Try this one, in a new workbook in a new Excel instance -

    ' in ThisWorkbook module
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If MsgBox("abort save?", vbYesNo) = vbYes Then
       Cancel = True
    End If
    End Sub

    Peter Thornton

    Thursday, March 03, 2011 9:57 PM
  • I pasted your code into a freshly opened copy of Excel and it worked exactly as advertised - intercepted the Save and continued with the save or aborted it as directed.  It worked the same for a directed save (the diskette icon) or a save request when closing the workbook.

    I made a copy and changed the name to BeforeClose and eliminated the UI variable. That worked exacly as my previous code - showed the message and ignored my response. I tried using the pull down to create the template and pasted the code into the template, with the same result.

    In both cases of the BeforeClose, at the Excel default save, if I selected Yes, the BeforeSave executed correctly - abort Yes returned to the Excel default save message, which was the prior state. Whatever is causing the BeforeClose Cancel to be ignored has no apparent effect on BeforeSave Cancel.

    This would be funny if it weren't wasting both our time. Cancelling the Close in BeforeClose was an afterthought to avoid an unreasonable combination of user actions.

    There being no under-documented user switches that I could throw to tailor the macro security by feature, (my primary hope) and since both computers I have tried work the same way, I conclude the situation is endemic to our configuration.

    I agree that if the problem originated from an MS patch, there would be many complaints. (My secondary hope, dashed early in my internet search for answers.)

    If it was introduced by our IT in applying  the MS patch, I could well be the first of our 120,000+ to do anything exactly like this. Also the error may well have been much more local in origin or impact, since this project has some unique infosec configurations.

    I thank you for your suggestions and particularly the code fragment known to work in other environments. If you come across any other ideas, I would appreciate inputs since what was a minor afterthought here (but became an object of my OCPD) may be important to some future use. I will try it again occasionally, just to see if it magically starts working.

    Thursday, March 03, 2011 11:18 PM