Displaying when workbook was last updated
 

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

Displaying when workbook was last updated
 

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

"Sapper" wrote in message
...
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

Displaying when workbook was last updated
 

"Bob Phillips" wrote:

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

"Sapper" wrote in message
...
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




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

Displaying when workbook was last updated
 

Sapper

Alt + F11 to open the Visual Basic Editor.

CTRL + r to open the Project Explorer.

Right-click on your workbook(project) and InsertModule.

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

On Wed, 17 Jun 2009 09:39:02 -0700, Sapper
wrote:



"Bob Phillips" wrote:

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

"Sapper" wrote in message
...
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




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

Displaying when workbook was last updated
 

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.

"Sapper" wrote:

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

Displaying when workbook was last updated
 

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


Gord

On Fri, 19 Jun 2009 06:21:19 -0700, Sapper
wrote:

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.

"Sapper" wrote:

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