[LizzardLen]

Hi there,
I came across this thread (now closed)
http://forums.techguy.org/business-a...time-last.html

Adding Date and Time Last Modified to footer of an Excel worksheet.

It adds the date to each worksheet.
Could someone please show me how to modify it to add the date to each worksheet ONLY IF that particular worksheet has changed?

Thank you in advance.

[Zack Barresse]

Hi there,

Define "change". As in, any change whatsoever? And did you use the workbook before save event? Use a workbook event to get what you want, just use the workbook sheet change event (still in ThisWorkbook module), and be sure you change the activesheet reference (in your linked thread) to the sheet object name (in the parenthesis of the event, I believe it is "Sh"). That should pretty much be it. If you need more help, post your code and tell us exactly what you are looking to do in a little greater detail.

HTH

[LizzardLen]

Thanks so much for quick replies.

Techtalk: I think that function would be volatile and always update whether a worksheet is edited or not.

Zack: Originally (based on the prior thread) I had:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
For Each Sheet In ThisWorkbook.Sheets
Sheet.PageSetup.LeftFooter = "Last saved: " & Format(Date, "dd-mm-yy") & " " & Time
Next Sheet
End Sub

Which changed the date for all worksheets whether changed or not.

Using your suggestion:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
Sh.PageSetup.LeftFooter = "Last saved: " & Format(Date, "dd-mm-yy") & " " & Time
End Sub

This does what I want. However, it would run constantly while working on a spreadsheet and I guess slowing it down. I'd like something more elegant (the frustrated pascal 1.01 programmer in me) such as:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
For Each Sheet In ThisWorkbook.Sheets
If Dirty
Sheet.PageSetup.LeftFooter = "Last saved: " & Format(Date, "dd-mm-yy") & " " & Time
Next Sheet
End Sub

Is there a way of implementing "If dirty"?

[Zack Barresse]

Yeah, by using a Workbook_SheetChange event. LOL! No real elegant way of doing that without having some kind of code running somewhere. And in Excel, there just isn't that many options.