displaying the latest save date -

[Derrick]

Hi!

Is it possible to have a cell function that will look like:
a b
1 Sheet Updated: (Latest saved date)

so that the date in B1 is the day of when it was saved last?
its for a data table, so that users know how up to date the information is.

thanks!

 

[Jacob Skaria]

Hi Derrick

You need to have a macro or a UDF. Try the below UDF

In any cell type the formula
=GetProperties("Last Save Time")

Function GetProperties(strType As String) As String
GetProperties = ThisWorkbook.BuiltinDocumentProperties(strType)
End Function

OR---------attach the code to an event. Set the Security level to low/medium
in (Tools|Macro|Security). From workbook launch VBE using short-key Alt+F11.
From the left treevew double click 'This Workbook ' and paste the below code
in the code panel...Adjust the Sheetname to suit your requirement..

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("Sheet1").Range('B1').Value = _
ThisWorkbook.BuiltinDocumentProperties("Last Save Time")
End Sub

 

[Luke M]

One note with the UDF, because it's not dependent on other cells, it won't
recalculate unless forced. This could lead to confusion, as you can save,
close, make changes & save, close, and the formula won't update (XL's "smart
calculation" rules kicking in). To force the formula to recheck last saved
date at least every time file is opened, you could throw in the NOW function,
like so:

=NOW()-NOW()+GetProperties("Last Save Time")
The formula now forces XL to check the save time any time a calculation is
performed. You may need to reset your cell formatting to show date/time.

 

[Derrick]

hi

thanks for the input so far. However, im getting a #Name? error, - its not
understanding the GetProperties() function.
just to make sure im doing it right - in XL 2003,
on the tab below for the sheet, click on 'view code'
then paste:
Function GetProperties(strType As String) As String
GetProperties = ThisWorkbook.BuiltinDocumentProperties(strType)
End Function
into the code under 'General' - and a GetProperties should appear to the
right under Declarations...
in my cell, type =Now() - Now() + GetProperties("Last Save Time")

 

[Luke M]

After you click on "view code" Press Alt+I, M (Insert - Module).
then paste the code. You are currently pasting into a sheet, needs to be in
a module.