1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

Discussion in 'Business Applications' started by JamieNJ, Jun 7, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. JamieNJ

    JamieNJ Thread Starter

    Joined:
    Jul 10, 2003
    Messages:
    161
    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
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,270
    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.
     
  3. JamieNJ

    JamieNJ Thread Starter

    Joined:
    Jul 10, 2003
    Messages:
    161
    Yes, last saved.
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,270
    See my edit, JamieNJ.
     
  5. JamieNJ

    JamieNJ Thread Starter

    Joined:
    Jul 10, 2003
    Messages:
    161
    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!
     
  6. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,270
    You'd better not! ;)

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

    rgds,
    bomb
     
  7. JamieNJ

    JamieNJ Thread Starter

    Joined:
    Jul 10, 2003
    Messages:
    161
    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?
     
  8. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,270
    That would be something like:

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

    JamieNJ Thread Starter

    Joined:
    Jul 10, 2003
    Messages:
    161
    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
     
  10. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,270
    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
     
  11. JamieNJ

    JamieNJ Thread Starter

    Joined:
    Jul 10, 2003
    Messages:
    161
    Hmm, still does not seem to work. Could you upload something?
     
  12. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,270
    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 :D ).
     

    Attached Files:

  13. JamieNJ

    JamieNJ Thread Starter

    Joined:
    Jul 10, 2003
    Messages:
    161
    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?
     
  14. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,270
    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? :)
     
  15. JamieNJ

    JamieNJ Thread Starter

    Joined:
    Jul 10, 2003
    Messages:
    161
    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?
     
  16. Sponsor

As Seen On
As Seen On...

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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/473412