The line of code you'd need to use is something like:
if thisworkbook.saved = true then
'it's saved
else
'it's dirty (not saved
end if
But the bad news is where to put it. You can change lots of things that will
make the workbook dirty, but not fire any event (like changing format, changing
a print title, changing code, ...).
But the very act of changing the format of the cell would make the workbook
dirty. But you could cheat a little and ignore this.
Kind of like this using a worksheet event:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ThisWorkbook.Saved = True Then
Me.Range("a1").Interior.ColorIndex = 3
ThisWorkbook.Saved = True
Else
Me.Range("a1").Interior.ColorIndex = 6
End If
End Sub
A couple of warnings -- most macros that do anything will destroy the Undo/Redo
stack and clear the clipboard.
Either of these are enough for me not to do this.
I'd just add a macro that displayed the status:
Option Explicit
Sub testme()
With ActiveWorkbook
MsgBox .FullName & vbLf & "Saved: " & .Saved
End With
End Sub
Put it in my personal.xl* file and assign it a shortcut key.
Then run it when I wanted to know.