Notify the user (change a cell value) when the workbook is being saved (i.e 'Saving...") a-CodePudding

I have macro that lets the user know how much time has past since the they last saved the workbook (it helps them know when they should save again), and it work great. Now I want to do this:

When the user saves the file, I want to note this to the user, by changing a cell value to 'Saving..." (while the saving process is being done), and when saving is finished - change that value to 'Saved'. (it takes a few seconds to save the file)

I tried this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Sheet4.Range("b3").Value = "Saving..."
End Sub

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    Sheet4.Range("b3").Value = "Saved"
End Sub

It actually works OK expected for one little thing:

When the AfterSave fires, it changes the cell value - after file was saved, and that means that the user would need to save again if they attempt to close the file right after they had just saved.

I then tried this, to prevent the seemingly redundant second-time saving:

 Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   Sheet4.Range("b3").Value = "Saving..."
   Application.ScreenUpdating = False
   Sheet4.Range("b3").Value = "Saved"
End Sub

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
   Application.ScreenUpdating = True
End Sub

I thought that if I set ScreenUpdating=False and do the cell change (before saving), it would work, but for some reason it doesn't work. The cell's value is changed to 'Saved' before the saving executes (while ScreenUpdating=False).

Any idea how to get it to work?

CodePudding user response：

Keep your first attempt and Add this to the bottom of the second one:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Sheet4.Range("b3").Value = "Saving..."
End Sub

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    Sheet4.Range("b3").Value = "Saved"
    ThisWorkbook.Saved = True
End Sub

This may work, it tricks the workbook to think that is has been saved and will let you close it without needing to save the second time after B3 was changed to Saved. However the next time you open the Workbook it will say "Saving..." on B3 instead of "Saved", you can clear B3 on Workbook_Open() and that should work.