Workbook_AfterSave() fake event-VBForums
Results 1 to 3 of 3

Thread: Workbook_AfterSave() fake event

  1. #1

    Thread Starter
    Head Hunted anhn's Avatar
    Join Date
    Aug 2007

    Workbook_AfterSave() fake event

    In ThisWorkbook module:
    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
    • Don't forget to use [CODE]your code here[/CODE] when posting code
    • If your question was answered please use Thread Tools to mark your thread [RESOLVED]
    • Don't forget to RATE helpful posts

    Baby Steps a guided tour
    IsDigits() and IsNumber() functions Wichmann-Hill Random() function >> and << functions for VB CopyFileByChunk

  2. #2
    New Member
    Join Date
    Jun 2014

    Re: Workbook_AfterSave() fake event


    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?

    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()
    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,


  3. #3
    New Member
    Join Date
    Jun 2014

    Re: Workbook_AfterSave() fake event

    Please note that I have cross-posted this here:

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts


Click Here to Expand Forum to Full Width

Survey posted by VBForums.