Can't close book programmatically inside of Workbook_BeforeClose if triggered programmatically

[johnywhy]

i'd like to intercept BeforeClose with my own process, and cancel the default process. Not working as expected:

Create a new workbook, enter the following, and save:

ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
          Cancel = True
          Application.EnableEvents = False  ' to prevent recursively calling this procedure
          ThisWorkbook.Close
End Sub

On the Excel front-end, close the workbook manually. It will close. Beware, EnableEvents will now be false. Close and reopen Excel to restore it, or enter in immediate pane:
Application.EnableEvents = True

Now reopen the same workbook. Put a breakpoint on Workbook_BeforeClose. Go to front end and again manually close the workbook. Step through the code, to confirm what it's doing.

Restore EnableEvents.

Open the file once more. Go to immediate pane, and enter:
ThisWorkbook.Close

The file will not close. Step through the code to see what's happening. Still doesn't close.

Why not? Any ideas?

 

[sykes]

Can't you just allow it to close using its own event, but still run your code beforehand:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Debug.Print "The"
Do_other_stuff
call other_procedures
End Sub

 

[johnywhy]

Can't you just allow it to close using its own event, but still run your code beforehand:

Nope.

Can you say why this doesn't work as expected? Am i using the Cancel parameter incorrectly? Is there some other Application setting that's needed to make it work?

 

[sykes]

'fraid not. Soz.

 

[johnywhy]

Can't you just allow it to close using its own event, but still run your code beforehand:

I can't because those other procedures also fail when the close is triggered programmatically.

Why does programmatically closing a workbook break OnTime?

(also posted to excelbanter.com and excelforum.com) Why does programmatically closing a workbook break OnTime? This code runs fine: ' ThisWorkbook: Dim WithEvents oApp As Application Private Sub Workbook_Open() Set oApp = Application End Sub Private Sub...

www.mrexcel.com

 

[Joe4]

In cases like this, it is often better for you to explain what it is you are trying to accomplish (i.e. what is supposed to be happening before closing).
If we have a better understanding of what you are ultimately trying to accomplish and why, we may be able to propose other alternatives to the process you are trying.

 

[johnywhy]

In cases like this, it is often better for you to explain what it is you are trying to accomplish (i.e. what is supposed to be happening before closing).
If we have a better understanding of what you are ultimately trying to accomplish and why, we may be able to propose other alternatives to the process you are trying.

@Joe4 i have done so. Plz see link above.

While i appreciate your desire to help me find some other way to accomplish what i want, this is what i want to accomplish. i desire to understand why this behavior is happening, and not just work around it.

Thx.

 

[Joe4]

i have done so. Plz see link above. Thx.

It looks like another very specific question, but does not really give us the big picture of the what & why (i.e. what does your data look like, what you are trying to ultimately accomplish, etc).

Anyway, if you are delving into Excel "bugs", there probably isn't much I can offer that hasn't already been said.

 

[johnywhy]

It looks like another very specific question, but does not really give us the big picture

While i appreciate your desire to help me with the big picture, for now i'd like to learn more about this specific Excel behavior. The big picture is all my projects, all my work, all my Excel VBA programming (about 25 years so far and counting).

if you are delving into Excel "bugs"

i don't know if it's a bug, a quirk, or a feature. Excel has a variety of quirks, and there are a variety tricks to handle them. Hoping someone out there has experience with this one!

thx