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 footer of an Excel worksheet


(!)

JamieNJ's Avatar
JamieNJ JamieNJ is offline JamieNJ has a Profile Picture
Member with 161 posts.
THREAD STARTER
 
Join Date: Jul 2003
07-Jun-2006, 07:44 AM #1
Solved: Adding Date and Time Last Modified to footer of an Excel worksheet
Is there a way to add Date and Time Last Modified to an Excel worksheet footer, so I can can tell from the various "work in progress" hardcopy printouts what is the latest version?

Thanks-Jamie
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
07-Jun-2006, 08:26 AM #2
Do you mean when last saved?

EDIT: you can do this with code.

If the window is maximised, you should see an Excel icon to the left of "File" in the menu bar. Rightclick it & choose "View Code".

Paste the following into the window that appears (the workbook module):

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveSheet.PageSetup.LeftFooter = "Last saved: " & Format(Date, "dd-mm-yy") & " " & Time
End Sub

Press ALT+Q to quit the Visual Basic Editor. Then save the file & check the footer in Print Preview.

Last edited by bomb #21; 07-Jun-2006 at 08:35 AM.. Reason: sp
JamieNJ's Avatar
JamieNJ JamieNJ is offline JamieNJ has a Profile Picture
Member with 161 posts.
THREAD STARTER
 
Join Date: Jul 2003
07-Jun-2006, 08:34 AM #3
Yes, last saved.
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
07-Jun-2006, 08:36 AM #4
See my edit, JamieNJ.
JamieNJ's Avatar
JamieNJ JamieNJ is offline JamieNJ has a Profile Picture
Member with 161 posts.
THREAD STARTER
 
Join Date: Jul 2003
07-Jun-2006, 08:43 AM #5
Awesome!! Naturally I had to make it US-centric, by changing the date format (I won't say to the "right" way), but that's the type of thing we do over here. Thanks much!
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
07-Jun-2006, 08:46 AM #6
Quote:
Originally Posted by JamieNJ
I won't say to the "right" way ...
You'd better not!

You're welcome tho' ; use "Thread Tools" to "Mark Solved" if you wish.

rgds,
bomb
JamieNJ's Avatar
JamieNJ JamieNJ is offline JamieNJ has a Profile Picture
Member with 161 posts.
THREAD STARTER
 
Join Date: Jul 2003
09-Jun-2006, 10:51 AM #7
A follow-up question: is there a modification to this code to have it apply to all worksheets in a workbook. E.g., I have a workbook with four worksheets. When I did the above, it dutifully put the Last Saved into the worksheet that was active when I saved the workbook. However, it did not add the footer to the other worksheets. I then switched to a different worksheet, and saved the Excel workbook again. The second worksheet now gets the footer, but with a timestamp that is different from the first worksheet. I.e., the first worksheet does not get the updated timestamp. Is there a way to ensure all worksheets get footers reflecting the same date and time?
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
09-Jun-2006, 03:13 PM #8
That would be something like:

For Each Sheet In ThisWorkbook.Sheets
Sheet.PageSetup.LeftFooter = "Last saved: " & Format(Date, "dd-mm-yy") & " " & Time
Next Sheet
JamieNJ's Avatar
JamieNJ JamieNJ is offline JamieNJ has a Profile Picture
Member with 161 posts.
THREAD STARTER
 
Join Date: Jul 2003
12-Jun-2006, 08:37 AM #9
Thanks. Sorry for the delayed response, I took the weekend off from thinking...

Can you provide me some more details on what you are suggesting? I.e., when I right-click on the Excel icon, and click View Code, the top left panel has a folder(Microsoft Excel Objects) with a bunch of nodes under it for each of the worksheets in my workbook. I clicked on one of the nodes, copied and pasted the below in, and did ALT-Q to get out of VBA Editor. Then I saved the Excel workbook, and printed out the worksheet in question, and there is no footer on it, with the Date/Time last modified. Should there be? Incidentally, I'm on Excel 2002.


Sheet.PageSetup.LeftFooter = "Last saved: " & Format(Date, "dd-mm-yy") & " " & Time
Next Sheet
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
13-Jun-2006, 10:18 AM #10
You've got 2 bits slightly wrong.

1. "the top left panel has a folder(Microsoft Excel Objects) with a bunch of nodes under it for each of the worksheets in my workbook. I clicked on one of the nodes ..."

This sounds like you're putting the code in one of the worksheet modules. It needs to go in the workbook module. Under the Objects folder, you've got Sheet1, Sheet2, etc. The bottom one should be "ThisWorkbook". That's where you need it. Once you're "there", you'll know from the title bar -- BookWhatever [ThisWorkbook (Code)]

2. You need all 3 code lines, i.e.:

For Each Sheet In ThisWorkbook.Sheets
Sheet.PageSetup.LeftFooter = "Last saved: " & Format(Date, "dd-mm-yy") & " " & Time
Next Sheet


Have another go. Shout if you're still stuck & I'll upload a sample.

rgds,
bomb
JamieNJ's Avatar
JamieNJ JamieNJ is offline JamieNJ has a Profile Picture
Member with 161 posts.
THREAD STARTER
 
Join Date: Jul 2003
14-Jun-2006, 11:11 AM #11
Hmm, still does not seem to work. Could you upload something?
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
14-Jun-2006, 12:44 PM #12
Certainly can. The attached has 3 sheets with footers:

Last saved: 14-06-06 18:41:06

Once you save it, they should update (or backdate, since you're in an earlier zone ).
Attached Files
File Type: xls Footer Date and Time.xls (30.0 KB, 3298 views)
JamieNJ's Avatar
JamieNJ JamieNJ is offline JamieNJ has a Profile Picture
Member with 161 posts.
THREAD STARTER
 
Join Date: Jul 2003
14-Jun-2006, 01:29 PM #13
OK, I seem to have it working. I was just putting in the three lines of VBA code you had up there, without surrounding it by the first and last line (i.e.,
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
and
End Sub
Once I put all five lines of code in there, it's working. Does that make sense? I'm not a VBA coder, as you can probably tell.

So, is this basically adding a macro to the workbook? What triggers this running? When you open the workbook? Just so I know, will this cause that dialogue box to pop up whenever I open the workbook, the one about enabling macros?
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
14-Jun-2006, 02:07 PM #14
That's correct. Essentially macros (AKA sub-procedures) have 3 bits -- a start line (Sub Whatever()), an end line (End Sub), and the actual code in the middle.

It's a BeforeSave event procedure, so it'll run every time (just before) you perform a save.

It will cause that dialogue box to pop up whenever you open the workbook. The only way round that is to have a macro to open the file stored in your "Personal Macro Workbook" and then assign that macro to a custom button (or keyboard shortcut).

Interesting stuff tho', eh?
JamieNJ's Avatar
JamieNJ JamieNJ is offline JamieNJ has a Profile Picture
Member with 161 posts.
THREAD STARTER
 
Join Date: Jul 2003
14-Jun-2006, 04:50 PM #15
Yes, very cool. I get it. So this particular sub procedure HAD to be named "Workbook_BeforeSave" I assume. I think I will skip the additional macro to open the workbook. I don't mind the macro warning when I open it.

Is there a painless way to have this macro be included in every new Excel workbook I create?
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 ↑