Date File Modified (2002)

Topic

New Reply

Is there a method by which a date can be automatically updated each time the file is changed. For example, if I have todays date in cell A1 and then I open the file and modify it tomorrow, is it possible to automatically change the date to tomorrows date when this change takes place?

Reply | Quote

Replies

(Edited by sdckapr on 01-Aug-05 10:26. Oops, forgot to disable events, to prevent recursive calls…)

You could add some code, like this, to the thisworkbook object:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Application.enableevents = false
    Worksheets(1).Range("A1").Value = Date
    Application.enableevents = true
End Sub

Change the sheet to store the date as appropriate. Beware, any change to any cell will change the date. Having code like this in the workbook, will clear the UNDO stack (you will no longer have undo).

Steve

Reply | Quote

You can create a Workbook_BeforeSave event handler:
– Activate the Visual Basic Editor (Alt+F11)
– Double click ThisWorkbook.
– Type or copy/paste the following code, modifying it as needed:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
WorkSheets(“Sheet1”).Range(“A1”) = Date
End Sub

– Each time the workbook is saved, the value in cell A1 on Sheet1 will be updated; if the workbook is closed without saving it, the value will remain unchanged.

Reply | Quote

A before save makes more sense than a change event.

That is what I get for answering the question instead of determining the “real question” Steve

Reply | Quote

Hans, thanks. I have tried, but must be doing something incorrectly.

I have the target file open. Select Alt+f11 which opens VB. I paste the code into the editor then go back to the file and make change, save but don’t see anything happen in A1. I may be missing it by not double clicking “ThisWorkbook” but don’t see that as an option…

Reply | Quote

It won’t work if you put the code in a standard module, it MUST be in ThisWorkbook. See screenshot below.

Reply | Quote

Get this: Runtime error 9. Subscript out of range…

Reply | Quote

Do you have a worksheet named “Sheet1” in the workbook? If not, then you need to modify the code to change the sheet name to the sheet where you want the date inserted.

Reply | Quote

Tried but still get error. Here is file.

Reply | Quote

(Edited by sdckapr on 01-Aug-05 15:02. Added PS)

Try:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Worksheets("DetailFinancialAnalysis").Range("A1") = Date
End Sub

Steve
PS. Or are you after:
Worksheets(“DetailFinancialAnalysis”).Range(“B2”) = Date

Reply | Quote

I don’t understand – there is no code in your workbook. No wonder it doesn’t work.

Reply | Quote

Your workbook did not have any code in it, so I can not tell you what you were doing wrong. Try the attached workbook.

Reply | Quote

You did not attach a workbook…

Steve

Reply | Quote

Thanks. I am having connection problems, and the attachment apparently got dropped when the link went down again.

Reply | Quote

I had code in it, but had to delete to get file small enough to send. This works. The .value at the end of the 2nd line must have been the difference.

Thanks for all the help and sorry if I caused any confusion.

Reply | Quote

In

WorkSheets(“Sheet1”).Range(“A1”) = Date

you must edit “Sheet1” to the name of an actual existing woprksheet in the workbook.

Reply | Quote