Cancel Change Event When Saving File

[Chippe01]

I have a workbook to create a daily dashboard for our group. I have created VBA code that automatically updates a particular cell with the last save date & time when the file is saved.

I had the code in the BeforeSave event, but the problem is that if I cancel the save operation the cell date and time still gets updated. I tried using the Cancel flag and running the cell update code only if cancel is false, but it still updates the target cell with the latest date and time.

So then I put the code in the AfterSave event and it removes the cancel issue. However, after the file is saved, If I try to close the file I am prompted to save the file again.

So, I changed the code to read as follows:

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
If Success Then
Application.EnableEvents = False
Sheets ("Daily Dashboard").Range.("V23").Value = "LAST SAVED " & Now
Application.EnableEvents = True
End If
End Sub

However, this still does not prevent Excel from prompting me to save the file if I try to close it.

What am I doing wrong?

 

[DanteAmor]

Try this

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    Application.EnableEvents = False
    Sheets("Daily Dashboard").Range("V23").Value = "LAST SAVED " & Now
    ThisWorkbook.Save
    Application.EnableEvents = True
End Sub

 

[Chippe01]

Oddly enough, I DID try what you suggested, however, I put the workbook save statement AFTER the EnableEvents statement, and it just kept saving the file in an endless loop. I had a hard time trying to get the macro to stop running.

But your suggestion works perfectly. Thank you.

It's funny how things like that sneak up on you.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.