Why does VBA Workbook.Close Statement Fail Inside Workbook_BeforeClose, If Close is Triggered Programmatically?

user_209942 0
Viewed 642

i'd like to intercept BeforeClose with my own process, and cancel the default process. Not working as expected. To replicate, 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 by clicking the close-X. It will close, as expected, by the ThisWorkbook.Close statement. In Excel 2016 and 365, if other workbooks are open, they remain open.

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. Still works fine.

Restore EnableEvents as described above.

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?

event-driven events vba excel
2 Answers

Consider this code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
          MsgBox "Start"
          ThisWorkbook.Close
          ThisWorkbook.Close
          MsgBox "End"
End Sub

This seems to confirm my comment.
If ThisWorkbook.Close could call ThisWorkbook.Close, this would indefinitely spam "Start" message boxes.

Closing the book normally gives you two "start" and one "end"

  • So it goes back to the start of BeforeClose on ThisWorkbook.Close the first time, then ignores it.

Closing it with ThisWorkbook.Close gives one "start and one "end"

  • Because it ignores both ThisWorkbook.Close

But

in this case, starting with ThisWorkbook.Close should have them always behave the same. And this isn't the case.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ThisWorkbook.Close

    ThisWorkbook.Close

    Cancel = True
    Application.EnableEvents = False  ' to prevent recursively calling this procedure

    ThisWorkbook.Close
End Sub

Take a look how this code executes when closed with ThisWorkbook.Close:
enter image description here It doesn't close

And then from the menu:
enter image description here It closes

If we don't disable the events, running it from ThisWorkbook.Closebehaves exactly the same. Steps through, doesn't close.
From the menu however, it's quite different:
enter image description here And doesn't close.

So it seems that calling Workbook_BeforeCloseusing ThisWorkbook.Close will ignore all other ThisWorkbook.Close, but closing the document with the menu will try to run each ThisWorkbook.Close once.

user_71311220

On the Excel front-end, close the workbook manually. It will close, as expected, by the ThisWorkbook.Close statement.

well, not quite:

If you close from the UI by clicking the Top Right X, the the described behviour dose occur. On the other hand, if you close from the UI by going to File/Close then the behaviour mirrors the immediate window beviour described (doesn't close)

From the documentation

Cancel Required Boolean

False when the event occurs. If the event procedure sets this argument to True, the close operation stops and the workbook is left open.

This explains the observed behaviour.

If you set cancel = True just before ThisWorkbook.Close then it does close.

So, why does clicking the X close it?

I believe it's because that UI is also exiting the Excel Application. The workbook close as a side effect of the Application Quitting, as do all open workbooks.

user_4454250
Related