How to Fire OnTime Event by a Workbook.Close Statement?

Question

It appears that if the OnTime event is registered by a programmatic MyBook.Close statement, then OnTime never runs.

This code works fine:

Sub TestOnTime()
          Application.OnTime Now + TimeValue("00:00:05"), "MySub"
End Sub


Sub MySub()
          Debug.Print "hello"
End Sub

Run TestOnTime. MySub will execute, as expected.

---

And this code runs fine:

ThisWorkbook:

Dim WithEvents oApp As Application


Private Sub Workbook_Open()
          Set oApp = Application
End Sub


Private Sub oApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
          Application.OnTime Now + TimeValue("00:00:05"), "MySub"
End Sub

Module 1:

Sub MySub()
          Debug.Print "hello"
End Sub

1. Manually close another workbook to fire oApp_WorkbookBeforeClose.
2. MySub executes, as expected.

---

But this code fails. The OnTime event never runs.

Book 1

ThisWorkbook:

Dim WithEvents oApp As Application


Private Sub Workbook_Open()
          Set oApp = Application
End Sub


Private Sub oApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
          Application.OnTime Now + TimeValue("00:00:05"), "MySub"
End Sub

Module 1:

Sub MySub()
          Debug.Print "hello"
End Sub

Book 2

Module 1:

Sub Test()
          ThisWorkbook.Close
End Sub

1. Run Test to close Book 2.
2. Book 1 oApp_WorkbookBeforeClose executes, as expected.
3. But the Book 1 MySub event never runs.

Why?

Why doesn't OnTime execute if registered by a Workbook_BeforeClose event? No code is running in the book that's closing. OnTime works no problem with other events (eg programmatically opening a workbook). Somehow, closing a workbook programmatically breaks OnTime. Why?

Answer 1

As Book 2 is being closed, You should include the Application.OnTime procedure in Book 2 and not in Book 1

Also, I think those books should be saved once and not new books.

Sub test()
Application.OnTime Now + TimeValue("00:00:05"), "Book 1.xlsm!MySub"
ThisWorkbook.Close
End Sub

EDIT Jul 6 -

You are closing the workbook and then you are trying to run a macro MySub in the same workbook after 5 seconds. Macro in the same workbook will not run once the book is closed. Application will reopen the file to run the macro. If you want to close Book2 after 5 seconds of closing Thisworkbook then --

in Thisworkbook

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime Now + TimeValue("00:00:05"), "Book2.xlsm!Test"
End Sub

So, after closing Thisworkbook, macro named "Test" in Book2 will run and will close that workbook.