Displaying when workbook was last updated

[Sapper]

Hi
I am using excel 2003. I want to show when a workbook was last updated
because several people access the data, which can only updated by one
authorised person.
I used the formula (straight from the book) ......= This workbook lasted
updated: "& TEXT(NOW( ), "mm/dd/yy/ hh:mm")
The book says that the NOW function will only update when I save or
recalculate, but it updates every time that the worksheet is open.
The user then believes that the data is up to date when that may not be the
case.
Can you help please
Thank you

 

[Sapper]

That is because Now is a volatile function, and open recalculates any
volatile functions.

Use a UDF


'-----------------------------------------------------------------
Function DocProps(prop As String)
'-----------------------------------------------------------------
Application.Volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function


and enter in a cell such as
=DocProps ("last author")
or
=DocProps ("last save time")



--
__________________________________
HTH

Bob




Bob, thanks for explaining why I dont get the result I want, unfortuneately I have no idea what a UVF is and what I have to do before I enter ....= DocProps etc.

Can you shed more light?

Thanks

 

[Gord Dibben]

Sapper

Alt + F11 to open the Visual Basic Editor.

CTRL + r to open the Project Explorer.

Right-click on your workbook(project) and Insert>Module.

Copy/paste Bob's DocProps UDF into that module.

Alt + q to return to the Excel window.

In A1(or a cell of your choice) of a sheet enter =DocProps("last save
time")

Format as Date or custom format.


Gord Dibben MS Excel MVP

 

[Sapper]

Many thanks to Bob and Gord. I have still no idea what a UDF is but and how
the syntax is worked out but I got the result that I wanted.

 

[Gord Dibben]

User Defined Function.................UDF


Gord

Many thanks to Bob and Gord. I have still no idea what a UDF is but and how
the syntax is worked out but I got the result that I wanted.