Workbook_AfterSave() fake event
In ThisWorkbook module:
Code:
Option Explicit
Private bSaved As Boolean '-- used to pass value from BeforSave to AfterSave
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
bSaved = Me.Saved '-- remember the current Saved status
If Me.Saved Then Me.Saved = False '-- assume the workbook was not Saved
MsgBox "BeforeSave"
'-- Code for BeforeSave here if required
Application.OnTime Now, "ThisWorkbook.Workbook_AfterSave"
'-- the call to Workbook_AfterSave will be delayed until the saving is
'-- completed or cancelled
End Sub
Private Sub Workbook_AfterSave() '-- a fake event that will be called by BeforeSave
If Me.Saved = False Then '-- Save has been cancelled either in code or in SaveAsUI
If bSaved Then Me.Saved = True '-- reset to previous Saved status
Exit Sub '-- Save was not occured so cancel AfterSave
End If
MsgBox "AfterSave"
'-- Code for AfterSave here
End Sub
Re: Workbook_AfterSave() fake event
Hi,
I found this very helpful and have been using it but recently noticed a problem and was wondering if anyone had a workaround.
I made a very simple workbook to test this. If I have the following three procedures in the ThisWorkbook module.
If I click the "Save" button in Excel, I see both messages.
However, if I run the Test subroutine, 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?
Code:
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
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.
Thank you very much in advance,
Regards
Re: Workbook_AfterSave() fake event
