Excel VBA Show Saved & Not Saved Status in a Cell in ActiveSheet

Question

I need to show Saved or Not Saved Status in the ActiveSheet in Cell C1 depending on the saved status and running the code from the Worksheet_Change

So if I have

Public Sub Worksheet_Change(ByVal Target As Range)
Call SaveStatus
End Sub

And

Public Sub SaveStatus()
    If ThisWorkbook.Saved = True Then
    ActiveSheet.Range("C1").Value = "Saved"
Else
    ActiveSheet.Range("C1").Value = "Not Saved"
End If
End Sub

I get error and the reason is getting the error "Method Range of Object _Worksheet failed" is because the Worksheet_Change event can be triggered by any change made to the worksheet, including changes made by VBA code. So when the SaveStatus sub updates the value of cell C1, it triggers the Worksheet_Change event again, which tries to update the value of cell C1 again, causing an infinite loop that eventually crashes Excel

How could I get to ignore the value in Cell C1

Edit

As suggested by @Warcupine But does not Work

Public Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Call SaveStatus
Application.EnableEvents = True
End Sub

Answer 1

I would like to submit one more answer to the question, which I initially did not appreciate, but in the end it has a more general look at the approach to the problem. What I mean: eg if you add a Sheet then the book should appear in "Not Saved" state, so you have to listen to every trigger that modifies the book which is tedious and I wanted to avoid that. So I thought that a timer that runs every 3 seconds (or whatever the user wants) for this particular issue would be a possible good solution. But what will the timer code do? After several tests I concluded that it is better to check the value of Application.Saved and accordingly change the value of a WorkSheet Name variable. So I created one, named _SAVED_STATE with book scope, to use in any Sheet I want. The good thing about this solution is that you don't need any code in the worksheets at all, only in two modules: the ThisWorkbook module and another one. On the other hand, you can display the status on any sheets you want and if necessary in a different place on each sheet. So here is the code:

'THIS CODE IN ThisWorkbook MODULE
'---------------------------------
Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   Call STOP_TIMER(False)
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   If SAVE_FLAG = True Then Exit Sub
   Cancel = True
   Call save_book
End Sub

Private Sub Workbook_Open()
   Call START_TIMER(TIMER_INTERVAL_DEFAULT)
End Sub



'THIS CODE IN ANOTHER MODULE
'----------------------------------
Option Explicit

Public Const TIMER_SUB = "timer_saved_state_sub"
Public Const SAVED_UNKNOWN = "=""Unknown"""
Public Const SAVED_YES = "=""Saved"""
Public Const SAVED_NO = "=""Not Saved"""
Public Const TIMER_INTERVAL_DEFAULT = "00:00:03"

Public SAVE_FLAG As Boolean
Private TIMER_INTERVAL As String
Private IN_TIMER As Long
Private nextTimerTime As Date

Public Sub writeSavedState(state As String)
   With ThisWorkbook.Names("_SAVED_STATE")
      If .Value <> state Then .Value = state
   End With
End Sub

Public Sub timer_saved_state_sub()
   If IN_TIMER <> 0 Then Exit Sub
   If Application.Ready Then
      IN_TIMER = IN_TIMER + 1
      Call writeSavedState(IIf(ThisWorkbook.Saved, SAVED_YES, SAVED_NO))
      IN_TIMER = IN_TIMER - 1
   End If
   If TIMER_INTERVAL = vbNullString Then TIMER_INTERVAL = TIMER_INTERVAL_DEFAULT
   nextTimerTime = Now() + TimeValue(TIMER_INTERVAL)
   Application.OnTime nextTimerTime, TIMER_SUB
End Sub

Public Sub STOP_TIMER(Optional saveTheState As Boolean = True)
   If nextTimerTime = 0 Then Exit Sub
   Do
      If IN_TIMER = 0 Then Exit Do
   Loop
   Application.OnTime nextTimerTime, TIMER_SUB, , Schedule:=False
   nextTimerTime = 0
   If saveTheState Then Call writeSavedState(SAVED_UNKNOWN)
End Sub

Public Sub START_TIMER(interval As String)
   If nextTimerTime = 0 Or ThisWorkbook.Names("_SAVED_STATE").Value = SAVED_UNKNOWN Then
      If interval <> vbNullString Then TIMER_INTERVAL = interval Else TIMER_INTERVAL = TIMER_INTERVAL_DEFAULT
      Call timer_saved_state_sub
   Else
      MsgBox ("timer_saved_state_sub NOT initiated")
   End If
End Sub

Public Sub set_timer_interval(interval As String)
   TIMER_INTERVAL = interval
End Sub

Public Sub save_book()
   On Error GoTo Lerr
   SAVE_FLAG = True
   Call writeSavedState(SAVED_YES)
   ThisWorkbook.Save
   SAVE_FLAG = False
   Exit Sub
Lerr:
   MsgBox (Err.Description)
   On Error GoTo 0
   SAVE_FLAG = False
   Call writeSavedState(SAVED_NO)
End Sub



'FOR EXAMPLE ONLY - THE CODE OF THE SHHET IN PICTURE
'---------------------------------------------------
Option Explicit

Private Sub BT_save_Click()
   Call save_book
End Sub

Private Sub TIMER_START_Click()
   Call START_TIMER(TIMER_INTERVAL_DEFAULT)
End Sub

Private Sub TIMER_STOP_Click()
   Call STOP_TIMER
End Sub

Answer 2

I have a solution so that when we display "Saved", this will be true so if the users closes the workbook then they are not prompted to save it. Every time activating a sheet, the C1 takes the current state "Saved" or "Not Saved". (comment: In excel 2019 there is not Workbook_AfterSave event)

Put this code in a module
-------------------------------------------

Public SAVE_FLAG  As Boolean
Public Const SAVED = "Saved"
Public Const NOTSAVED = "Not Saved"

Public Function WriteSaveState(Optional state As String = NOTSAVED)
    Application.EnableEvents = False
       For Each ws In Worksheets
          With ws.Range("C1")
             If s <> .value Then .value = s Else Exit For
          End With
       Next
    Application.EnableEvents = True
End Function

Public Sub save_book()
   On Error GoTo Lerr
   SAVE_FLAG = True
   Call WriteSaveState(SAVED)
   ThisWorkbook.Save
   SAVE_FLAG = False
   Exit Sub
Lerr:
   MsgBox (Err.Description)
   On Error GoTo 0
   SAVE_FLAG = False
   Call WriteSaveState(NOTSAVED)
End Sub



Put this code in ThisWorkBook module
---------------------------------------------------

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   If SAVE_FLAG = True Then Exit Sub
   Cancel = True
   Call save_book
End Sub

Private Sub Workbook_Open()
   Call WriteSaveState(IIf(ThisWorkbook.SAVED, SAVED, NOTSAVED))
End Sub



In every Sheet put this code:
----------------------------------------------
    
Private Sub Worksheet_Change(ByVal Target As Range)
   Call WriteSaveState()  'Optional default value = NOTSAVED
End Sub

Answer 3

To stop the error, you need to disable events to avoid an infinite loop.

Public Sub SaveStatus()
    Application.EnableEvents = False
    If ThisWorkbook.Saved = True Then
        ActiveSheet.Range("C1").Value = "Saved"
    Else
        ActiveSheet.Range("C1").Value = "Not Saved"
    End If
    Application.EnableEvents = True
End Sub

To have the value in C1 actually flip you'll need

Private Sub Worksheet_Change(ByVal Target As Range)
SaveStatus
End Sub

and

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
If Success Then
    SaveStatus
End If
end Sub

You could try and use something like Private Sub Worksheet_SelectionChange(ByVal Target As Range) But I personally wouldn't trust that to be accurate for your needs.