Seeing If a Workbook Has Been Saved

Dave notes that in Word, it's easy to see whether the document you're working on has been saved or not. In Excel, though, that information isn't presented, so he wonders if there is some way to have the program display info in a cell that would indicate whether or not the workbook has been saved.

In the latest versions of Excel, what Dave sees is already in place. The indication of whether a workbook has been saved or not is also consistent with the indication shown in Word. If a workbook has been saved, then at the top of the Excel program window—just to the right of the Quick Access Toolbar and to the left of the Search box—you'll see the name of the workbook. Immediately after saving the workbook, to the right of the workbook name you'll see text similar to "Saved to this PC." (In Microsoft Word, the text is simply "Saved.") Make an edit, and this text goes away until you next do a save.

If this indicator is not prominent enough for you, then you can create some macros that will do what Dave desires. Place the following macro in a standard module for the current workbook:

Private Sub UpdateSaveStatus(ByVal JustSaved As Boolean)
    On Error GoTo SafeExit
    Application.EnableEvents = False

    With ActiveWorkbook.ActiveSheet.Range("A1")
        If ActiveWorkbook.Saved Then
            .Value = "Saved"
        Else
            .Value = "Unsaved!"
        End If
    End With
    If JustSaved Then ActiveWorkbook.Saved = True

SafeExit:
    Application.EnableEvents = True
End Sub

Next, place the following macros in the ThisWorkbook module for the save workbook:

Private Sub Workbook_Open()
    UpdateSaveStatus False
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    UpdateSaveStatus False
End Sub

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    UpdateSaveStatus True
End Sub

Whenever the workbook is opened, saved, or a change is made, the UpdateSaveStatus macro is executed, which places the appropriate text ("Saved" or "Unsaved!") into cell A1.

The drawback to such an approach, obviously, is that your workbook will need to be saved as an XLSM file (macro enabled). The macro code would also need to be added to every workbook that you want to use this approach.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (5224) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.