Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. . |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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. . . |
#8
|
|||
|
|||
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. |
#9
|
|||
|
|||
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. |
#10
|
|||
|
|||
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. |
#11
|
|||
|
|||
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. |
#12
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Modified Date | Excel Discussion (Misc queries) | |||
Date updates from worksheet to chart & changes date to a date series! Help!! | Charts and Charting in Excel | |||
Date issue between Windows and Macintosh version | Excel Discussion (Misc queries) | |||
Date formula | Excel Discussion (Misc queries) | |||
Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel |