VBA: Application.OnTime does not work in Workbook_BeforeSave event if saved programmatically but does work if saved manually

[m1chael]

Hi,

I have noticed that Application.OnTime does not work in the ThisWorkbook module Workbook_BeforeSave event if the workbook is saved programmatically but it does work if saved manually.

I made a very simple workbook to test this.

If I have the following three procedures in the ThisWorkbook module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  MsgBox "Before Save"
  Application.OnTime Now(), "ThisWorkbook.Fake_Workbook_AfterSave"
End Sub

Public Sub Fake_Workbook_AfterSave()
  MsgBox "Fake After Save"
End Sub

Public Sub Test()
  ThisWorkbook.Save
End Sub

If I click the "Save" button in Excel, I see both messages: "Before Save" then "Fake After Save".

However, if I run the Test subroutine for example, I only see the "Before Save" message. For some reason, if saved programmatically, either Application.OnTime does not set the schedule or the schedule does not run.

Can anyone help or provide a workaround so that the fake After Save procedure will run regardless of whether or not the save was manual or by code?

I should point out that although I am using Excel 2010 and have access to the real Workbook_AfterSave event, some users of the spreadsheets do not, hence using the workaround in the first place.

Please note that I have cross-posted this also as a reply to the thread where I originally found the workaround that I am using: Workbook_AfterSave() fake event-VBForums

Michael​

 

[RoryA]

It looks like a bug to me. Simplest workaround is probably to explicitly call the fake aftersave code whenever you save the workbook in code.