Last Saved Date and Printed Date in Footer

[jluecht]

How can you show the last saved date in the footer? The only option that I can find is &[Date], which doesn't help, since this is simply today's date.
What I would like the footer to display is the last saved or modified date, and if the file is printed, I would like to include the printed date. Any suggestions??
I have searched the postings and did not find this particular question.
Thanks for your help!

 

[dsnbld]

lmrippey, using my example above this is what you could do.

Put the following in a project module
in personal.xls:

<pre>Sub Before_Print_Macro()

Application.ScreenUpdating = False
On Error GoTo NotSaved

For Each wsSheet In ActiveWorkbook.Worksheets
With wsSheet.PageSetup
.LeftFooter = "Last Modified on " & ActiveWorkbook.BuiltinDocumentProperties.Item("Last Save Time")
.RightFooter = "Printed on &D &T"
End With
Next
Application.ScreenUpdating = True

Exit Sub

NotSaved:
Application.ScreenUpdating = True
MsgBox "Before_Print_Macro cannot run until Book has been saved."

End Sub</pre>

Use the following in your active workbook:

<pre>Private Sub Workbook_BeforePrint(Cancel As Boolean)

Application.Run ("PERSONAL.xls!Before_Print_Macro")

End Sub</Pre>

OR

<pre>Private Sub CommandButton1_Click()

Application.Run ("PERSONAL.xls!Before_Print_Macro")

End Sub</Pre>

 

[Rocky E]

Hi Loriane,
dsnbld has offer some good code.
I have know about On Error, but for some unknown reason overlooked it here.
The point I believe you may be missing is Excel is Object Oriented.
If you print a worksheet in the Activeworkbook you must have code in the Activeworkbook.
That code can call macros in any open workbook including your Personal.xls.
An event in the Activeworkbook cannot trigger code in another workbook with some code of it's own.
I said I was not a teacher, nor an expert. They would have thought about both these things.
I just wanted to pass on the ActiveWorkbook.BuiltinDocumentProperties.Item("Last Save Time").
There are all kinds of issues with it. For example if you open an old workbook, make changes and print it without saving?


_________________
Hope this helps!

Rocky<h6>"Be not the first by whom the New are try'd,
Nor yet the last to lay the Old aside."
Alexander Pope (1688-1744).</h6>
This message was edited by Rocky E on 2002-07-25 06:31

 

[Marksaigon]

OK, not a problem. I am neither a teacher nor an expert, but I will hang with you until you have it working. Like I say this piece of code is a work in progress. The snippet was cut directly from one of my workbooks. The other guy got it to work, but he sounds pretty experienced to me.
Excel is a dynamic environment. Unless you install a fresh copy (but don't do that) the actual keystroke sequence might vary. Please follow these steps and tell me where you have trouble.
1) Start Excel clean so you have Book1
2) Type Hello World in A1. You have to have something to print.
3) Press alt-F11 you should see the Visual Basic window, yes/no?
4) In the left upper corner should be a smaller window Project - VBAProject if not press ctrl-R yes/no?
5) This looks a little like a directory tree. The last item in the tree should be ThisWorkbook Yes/no?
6) Double click ThisWorkbook and a new window should appear to the right titled Book1 - ThisWorkbook (Code) Yes/no?
7) Cut this code and paste it in the window.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Application.ScreenUpdating = False
    For Each wk In Worksheets
        With ActiveSheet.PageSetup
            .LeftHeader = "Last Modified on " & ActiveWorkbook.BuiltinDocumentProperties.Item("Last Save Time")
            .CenterHeader = ""
            .RightHeader = "Printed on &D &T"
            .LeftFooter = "&F &A"
            .CenterFooter = ""
            .RightFooter = "Page &P of &N"
        End With
     Next
     Application.ScreenUpdating = True
End Sub

Are we OK? yes/no
Save the workbook. You will get an error if you execute this code on a workbook that has not been saved.
9) Close the VBA Editor (press alt-F11 again. Then Print or Preview Have we got it this time? Yes/no?
I truly hope this helps!
This message was edited by Rocky E on 2002-07-12 20:24

It works, thanks. But, is there any way to change the format of the date?? I would like the month displayed in letters as opposed to numbers. I have changed my regional settings on my computer so in the bottom right hand corner of my monitor it reads 2019/December/05 but it still prints out 2019/12/05.