[JamieNJ]

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]

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.

[JamieNJ]

Yes, last saved.

[bomb #21]

See my edit, JamieNJ.

[JamieNJ]

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]

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]

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]

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]

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]

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]

Hmm, still does not seem to work. Could you upload something?

[bomb #21]

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 ).

[JamieNJ]

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]

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]

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?