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

johnywhy

New Member
Joined
Sep 12, 2008
Messages
47
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
  2. MacOS
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:
VBA Code:
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?
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,883
Office Version
  1. 365
Platform
  1. Windows
Can't you just allow it to close using its own event, but still run your code beforehand:

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

johnywhy

New Member
Joined
Sep 12, 2008
Messages
47
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
May 1, 2002
Messages
1,883
Office Version
  1. 365
Platform
  1. Windows
'fraid not. Soz.
 

johnywhy

New Member
Joined
Sep 12, 2008
Messages
47
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,028
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 12, 2008
Messages
47
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,028
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 12, 2008
Messages
47
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
  2. MacOS
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
 

Forum statistics

Threads
1,147,498
Messages
5,741,505
Members
423,663
Latest member
kaveh87rsh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top