Results 1 to 3 of 3

Thread: Workbook_AfterSave() fake event

  1. #1

    Thread Starter
    Head Hunted anhn's Avatar
    Join Date
    Aug 2007
    Location
    Australia
    Posts
    3,669

    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
    • 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
    Posts
    2

    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

  3. #3
    New Member
    Join Date
    Jun 2014
    Posts
    2

    Re: Workbook_AfterSave() fake event

    Please note that I have cross-posted this here: http://www.mrexcel.com/forum/excel-q...ml#post3850946

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