johnywhy
New Member
- Joined
- Sep 12, 2008
- Messages
- 47
- Office Version
- 365
- 2019
- 2016
- 2013
- 2011
- 2010
- 2007
- Platform
- Windows
- MacOS
(also posted to excelbanter.com and excelforum.com)
Why does programmatically closing a workbook break OnTime?
This code runs fine:
Manually close another workbook to fire oApp_WorkbookBeforeClose. MySub executes, as expected.
But, if instead of manually closing another book, you close another book programmatically, OnTime never runs. oApp_WorkbookBeforeClose does execute, so the OnTime is getting registered. But it never runs.
oApp_WorkbookBeforeClose runs. But MySub never runs.
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 Workbook_Open). Somehow, closing a workbook breaks OnTime.
Why does programmatically closing a workbook break OnTime?
This code runs fine:
VBA Code:
' 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 SubManually close another workbook to fire oApp_WorkbookBeforeClose. MySub executes, as expected.
But, if instead of manually closing another book, you close another book programmatically, OnTime never runs. oApp_WorkbookBeforeClose does execute, so the OnTime is getting registered. But it never runs.
VBA Code:
' Immediate pane
OtherWorkbook.CloseoApp_WorkbookBeforeClose runs. But MySub never runs.
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 Workbook_Open). Somehow, closing a workbook breaks OnTime.
Last edited:
