Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: Adding Date and Time Last Modified to an Excel worksheet


(!)

LizzardLen's Avatar
LizzardLen LizzardLen is offline
Junior Member with 2 posts.
THREAD STARTER
 
Join Date: Nov 2008
Experience: Intermediate
02-Nov-2008, 09:00 PM #1
Solved: Adding Date and Time Last Modified to an Excel worksheet
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's Avatar
Computer Specs
Member with 5,434 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
03-Nov-2008, 12:04 PM #2
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's Avatar
LizzardLen LizzardLen is offline
Junior Member with 2 posts.
THREAD STARTER
 
Join Date: Nov 2008
Experience: Intermediate
03-Nov-2008, 05:09 PM #3
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's Avatar
Computer Specs
Member with 5,434 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
03-Nov-2008, 06:23 PM #4
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.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑