Modified Date
 

Is there a formula or a way to input the last date a file was modified into
a cell? I have a spreadsheet that many users will be accessing but will not
be able to change. I want them to be able to see the last time the
information was updated. I could manually type in the date, but I know I
may forget sometime, so I want a cell that show the last modified date
automatically. Is this possible or am I crazy?

thanks for your help.

Hi
use the following UDF (only on workbook level):


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
=DOCPROPS("last save time")
(format cell as date)

--
Regards
Frank Kabel
Frankfurt, Germany

DME wrote:
Is there a formula or a way to input the last date a file was
modified into a cell? I have a spreadsheet that many users will be
accessing but will not be able to change. I want them to be able to
see the last time the information was updated. I could manually type
in the date, but I know I may forget sometime, so I want a cell that
show the last modified date automatically. Is this possible or am I
crazy?

thanks for your help.

it is possible and your not crazy.
in the workbook before save event put this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean,
Cancel As Boolean)
Sheets("Sheet1").Range("A1").FormulaR1C1 = Now()
Sheets("sheet1").Range("A2").FormulaR1C1 =
Application.UserName
End Sub

This will tell you the last time the wb was saved and who
did it. use what ever sheet and range you wish.

-----Original Message-----
Is there a formula or a way to input the last date a file
was modified into
a cell? I have a spreadsheet that many users will be
accessing but will not
be able to change. I want them to be able to see the
last time the
information was updated. I could manually type in the
date, but I know I
may forget sometime, so I want a cell that show the last
modified date
automatically. Is this possible or am I crazy?

thanks for your help.


.

Hi
this line
Sheets("Sheet1").Range("A1").FormulaR1C1 = Now()

should throw an error. You probably meant:
Sheets("Sheet1").Range("A1").FormulaR1C1 = "=Now()"
though this will update the value with each re-calculation


--
Regards
Frank Kabel
Frankfurt, Germany

wrote:
it is possible and your not crazy.
in the workbook before save event put this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean,
Cancel As Boolean)
Sheets("Sheet1").Range("A1").FormulaR1C1 = Now()
Sheets("sheet1").Range("A2").FormulaR1C1 =
Application.UserName
End Sub

This will tell you the last time the wb was saved and who
did it. use what ever sheet and range you wish.

-----Original Message-----
Is there a formula or a way to input the last date a file was
modified into a cell? I have a spreadsheet that many users will be
accessing but will not be able to change. I want them to be able to
see the last time the information was updated. I could manually
type in the date, but I know I may forget sometime, so I want a cell
that show the last modified date automatically. Is this possible or
am I crazy?

thanks for your help.


.

Why would it throw an error?

The parens are unnecessary, but otherwise it should work with the VBA
Now function.


In article ,
"Frank Kabel" wrote:

this line
Sheets("Sheet1").Range("A1").FormulaR1C1 = Now()

should throw an error. You probably meant:
Sheets("Sheet1").Range("A1").FormulaR1C1 = "=Now()"
though this will update the value with each re-calculation

Hi J.E.
my mistake, thanks for nthe correction!

--
Regards
Frank Kabel
Frankfurt, Germany

JE McGimpsey wrote:
Why would it throw an error?

The parens are unnecessary, but otherwise it should work with the VBA
Now function.


In article ,
"Frank Kabel" wrote:

this line
Sheets("Sheet1").Range("A1").FormulaR1C1 = Now()

should throw an error. You probably meant:
Sheets("Sheet1").Range("A1").FormulaR1C1 = "=Now()"
though this will update the value with each re-calculation

hi again
change now() to Date.

-----Original Message-----
it is possible and your not crazy.
in the workbook before save event put this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As
Boolean,
Cancel As Boolean)
Sheets("Sheet1").Range("A1").FormulaR1C1 = Now()
Sheets("sheet1").Range("A2").FormulaR1C1 =
Application.UserName
End Sub

This will tell you the last time the wb was saved and who
did it. use what ever sheet and range you wish.

-----Original Message-----
Is there a formula or a way to input the last date a
file
was modified into
a cell? I have a spreadsheet that many users will be
accessing but will not
be able to change. I want them to be able to see the
last time the
information was updated. I could manually type in the
date, but I know I
may forget sometime, so I want a cell that show the last
modified date
automatically. Is this possible or am I crazy?

thanks for your help.


.

.

OK, I am stupid. I copied and pasted the info you gave me into VBA (I think
that is what I was suppose to do?) and then enter =DOCPROPS in Cell A1. It
gives me a #Name? value. Tell Mr. Idiot here what I did wrong.

Thanks.



"Frank Kabel" wrote in message
...
Hi
use the following UDF (only on workbook level):


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
=DOCPROPS("last save time")
(format cell as date)

--
Regards
Frank Kabel
Frankfurt, Germany

DME wrote:
Is there a formula or a way to input the last date a file was
modified into a cell? I have a spreadsheet that many users will be
accessing but will not be able to change. I want them to be able to
see the last time the information was updated. I could manually type
in the date, but I know I may forget sometime, so I want a cell that
show the last modified date automatically. Is this possible or am I
crazy?

thanks for your help.

Hi
see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
for installing this macro

--
Regards
Frank Kabel
Frankfurt, Germany

DME wrote:
OK, I am stupid. I copied and pasted the info you gave me into VBA
(I think that is what I was suppose to do?) and then enter =DOCPROPS
in Cell A1. It gives me a #Name? value. Tell Mr. Idiot here what I
did wrong.

Thanks.



"Frank Kabel" wrote in message
...
Hi
use the following UDF (only on workbook level):


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
=DOCPROPS("last save time")
(format cell as date)

--
Regards
Frank Kabel
Frankfurt, Germany

DME wrote:
Is there a formula or a way to input the last date a file was
modified into a cell? I have a spreadsheet that many users will be
accessing but will not be able to change. I want them to be able to
see the last time the information was updated. I could manually
type in the date, but I know I may forget sometime, so I want a
cell that show the last modified date automatically. Is this
possible or am I crazy?

thanks for your help.

I think I will just type it in. I copied and pasted the entrire function
you gave me below into VBA under (this Workbook). It appears to accept it
and recognize it as a function. But when I enter =DOCPROPS I still get the
#name? . What could I possily be doing wrong.

I hate being pest, but I read the website you gave the link to and I still
am unable to master this little task.




"Frank Kabel" wrote in message
...
Hi
see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
for installing this macro

--
Regards
Frank Kabel
Frankfurt, Germany

DME wrote:
OK, I am stupid. I copied and pasted the info you gave me into VBA
(I think that is what I was suppose to do?) and then enter =DOCPROPS
in Cell A1. It gives me a #Name? value. Tell Mr. Idiot here what I
did wrong.

Thanks.



"Frank Kabel" wrote in message
...
Hi
use the following UDF (only on workbook level):


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
=DOCPROPS("last save time")
(format cell as date)

--
Regards
Frank Kabel
Frankfurt, Germany

DME wrote:
Is there a formula or a way to input the last date a file was
modified into a cell? I have a spreadsheet that many users will be
accessing but will not be able to change. I want them to be able to
see the last time the information was updated. I could manually
type in the date, but I know I may forget sometime, so I want a
cell that show the last modified date automatically. Is this
possible or am I crazy?

thanks for your help.

Hi
this is the wrong place. Create a new module in this project and insert the
code there (as described in the link I provided...)

--
Regards
Frank Kabel
Frankfurt, Germany

DME wrote:
I think I will just type it in. I copied and pasted the entrire
function you gave me below into VBA under (this Workbook). It
appears to accept it and recognize it as a function. But when I
enter =DOCPROPS I still get the #name? . What could I possily be
doing wrong.

I hate being pest, but I read the website you gave the link to and I
still am unable to master this little task.




"Frank Kabel" wrote in message
...
Hi
see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
for installing this macro

--
Regards
Frank Kabel
Frankfurt, Germany

DME wrote:
OK, I am stupid. I copied and pasted the info you gave me into VBA
(I think that is what I was suppose to do?) and then enter =DOCPROPS
in Cell A1. It gives me a #Name? value. Tell Mr. Idiot here what I
did wrong.

Thanks.



"Frank Kabel" wrote in message
...
Hi
use the following UDF (only on workbook level):


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
=DOCPROPS("last save time")
(format cell as date)

--
Regards
Frank Kabel
Frankfurt, Germany

DME wrote:
Is there a formula or a way to input the last date a file was
modified into a cell? I have a spreadsheet that many users will
be accessing but will not be able to change. I want them to be
able to see the last time the information was updated. I could
manually type in the date, but I know I may forget sometime, so I
want a cell that show the last modified date automatically. Is
this possible or am I crazy?

thanks for your help.

Frank,

Thanks for all your help. It WORKED! I can't tell you how many times I
come here and I read the help you provide to myself and the many other users
that just need a little guidance. Your patience and knowledge are very
valuable to us all.

Thanks Again!





"Frank Kabel" wrote in message
...
Hi
this is the wrong place. Create a new module in this project and insert
the
code there (as described in the link I provided...)

--
Regards
Frank Kabel
Frankfurt, Germany

DME wrote:
I think I will just type it in. I copied and pasted the entrire
function you gave me below into VBA under (this Workbook). It
appears to accept it and recognize it as a function. But when I
enter =DOCPROPS I still get the #name? . What could I possily be
doing wrong.

I hate being pest, but I read the website you gave the link to and I
still am unable to master this little task.




"Frank Kabel" wrote in message
...
Hi
see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
for installing this macro

--
Regards
Frank Kabel
Frankfurt, Germany

DME wrote:
OK, I am stupid. I copied and pasted the info you gave me into VBA
(I think that is what I was suppose to do?) and then enter =DOCPROPS
in Cell A1. It gives me a #Name? value. Tell Mr. Idiot here what I
did wrong.

Thanks.



"Frank Kabel" wrote in message
...
Hi
use the following UDF (only on workbook level):


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
=DOCPROPS("last save time")
(format cell as date)

--
Regards
Frank Kabel
Frankfurt, Germany

DME wrote:
Is there a formula or a way to input the last date a file was
modified into a cell? I have a spreadsheet that many users will
be accessing but will not be able to change. I want them to be
able to see the last time the information was updated. I could
manually type in the date, but I know I may forget sometime, so I
want a cell that show the last modified date automatically. Is
this possible or am I crazy?

thanks for your help.