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



## JamieNJ (Jul 11, 2003)

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 (Jul 1, 2005)

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 (Jul 11, 2003)

Yes, last saved.


----------



## bomb #21 (Jul 1, 2005)

See my edit, JamieNJ.


----------



## JamieNJ (Jul 11, 2003)

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 (Jul 1, 2005)

JamieNJ said:


> 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 (Jul 11, 2003)

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 (Jul 1, 2005)

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 (Jul 11, 2003)

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 (Jul 1, 2005)

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 work_sheet_ modules. It needs to go in the work_book_ 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 (Jul 11, 2003)

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


----------



## bomb #21 (Jul 1, 2005)

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 (Jul 11, 2003)

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 (Jul 1, 2005)

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 (Jul 11, 2003)

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?


----------



## bomb #21 (Jul 1, 2005)

Painless? Not entirely, IMO -- see Help for "Create a workbook template for new workbooks".


----------



## jjnc (Mar 5, 2007)

So how does one go about changing the time format for this macro? Thanks for any input!!


----------



## Perry M (May 24, 2007)

I was looking for a way to do this too. Glad I happened across this thread.
I would also like to find a way to put the last saved date into a cell on a worksheet. Is there an easy way to accomplish this??


----------



## Rollin_Again (Sep 4, 2003)

Perry M said:


> I was looking for a way to do this too. Glad I happened across this thread.
> I would also like to find a way to put the last saved date into a cell on a worksheet. Is there an easy way to accomplish this??


Just use this line in the Before_Save event instead of the code the Bomb posted earlier. Just change A1 to the cell of your choice.


```
Range("A1").Value = Format(Date, "mm-dd-yy")
```
Regards,
Rollin


----------

