Amy

You don't really need a UDF to return the date/time to a header/footer.

Sub Last_Saved_Footer()
'Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
For Each wkSht In ThisWorkbook.Worksheets
wkSht.PageSetup.RightFooter = "&8Last Saved : " & _
Format(ThisWorkbook.BuiltinDocumentProperties("Las t Save Time"), _
"yyyy-mmm-dd hh:mm:ss")
Next wkSht
End Sub


Gord Dibben MS Excel MVP

On Thu, 18 Jun 2009 08:44:01 -0700, Amy
wrote:

Let me see if I understand this correctly. What you mention below of what I
won't get is actually what I do want. I'm sorry if I did not ask my question
more clearly. let me try again using an example.

I have a file named Peppers.xls. I open the file, make some changes, save
it, and close it. The file properties show 6/16/09 xxtime after I close the
file.

Now, I edit the Peppers.xls file the next day, save my changes, and exit
Excel again. Now, the file properties show 6/17/09 xxtime after closing the
file.

How do I get the "6/17/09" information in to the header? If I use Paul's
version (yup, much more eloquent than my bullied attempt), will I get this as
the last time the file was saved? Each time I edit the file, I would like
that new date to show up in the header.

Hope that makes sense and thanks for your help!
- Amy

"JLatham" wrote:

Amy, to add to what Paul C gave you (a good way to do it, by the way). While
you could use the contents of the cell where you put the user defined
function, it would just require more code to get it from the cell to the
header. So it turns out to be not really needed, and the way Paul C gave you
is more efficient.

Also, unless my thinking is muddled this morning, remember that the entry
you get is going to be exactly that - the last saved date/time. Which means
it's not going to be the date/time that the current user saves the file at
the end of their session when it is next opened. If you (also) call the
routine during the workbook's _Open() event, it would be updated to the true
last time saved.

"Amy" wrote:

I am using Excel 2003. I created a Macro with the following custom function:

Function Last_Saved_Date()
Last_Saved_Date = Application.Caller.Parent.Parent. _
BuiltinDocumentProperties("Last Save Time").Value
End Function

Then in a cell in the worksheet, I have it as "=Last_Saved_Date()" and I
make sure to format the cell in a date format.

Doing this, I am able to see the last saved date inside the worksheet.

My questions are the following:

1. How do I have the header print at the top of each page, the last saved
date of the file? May I use the contents of my Last Saved Date cell?
2. Is there a more efficient way to do this in the header without using this
macro?

Thank you for your help,
Amy