MrExcel Publishing
Your One Stop for Excel Tips & Solutions

last updated date


Posted by darren on December 31, 2001 1:59 PM

this should be simple but i can't figure it out. i have a spreadsheet that i want to show the "last updated date" when i re-open it. help me please.


Posted by Scott on December 31, 2001 2:07 PM

There might be an easier way, but this will work too. If you paste this in to your workbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Range("A1").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("A1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub


This will enter in the date in A1, and then copy it in as values so that when you open it again on another day, you can see the last date.

Posted by Bariloche on December 31, 2001 2:19 PM

or, to simplify:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Cells(1,1).Value = Date

End Sub


Change the "Cells" specification as necessary.

enjoy

Posted by darren on December 31, 2001 2:29 PM

ok, i can get it to work if i run it as a macro, but i'm unable to run it automatically. i couldn't get it to work when i pasted into the module for the workbook. : this should be simple but i can't figure it out. i have a spreadsheet that i want to show the "last updated date" when i re-open it. help me please.


Posted by Bariloche on December 31, 2001 2:49 PM

darren,

The code provided was an "Event" procedure and needs to be put into the code sheet for the "ThisWorkbook" object. In the VB Editor, with the Project Explorer open you should be able to see the "ThisWorkbook" object, double click on it to open up its' code sheet and just paste Scott's code (or my simplification) in there.

Drop down the "Procedure" list to see the other "Events" that are available. These can come in handy ar other times. Each worksheet has a code sheet too with corresponding "Event" procedures available. You might want to investigate them also.


have fun

ok, i can get it to work if i run it as a macro, but i'm unable to run it automatically. i couldn't get it to work when i pasted into the module for the workbook.

Posted by darren on December 31, 2001 2:57 PM

The code provided was an "Event" procedure and needs to be put into the code sheet for the "ThisWorkbook" object. In the VB Editor, with the Project Explorer open you should be able to see the "ThisWorkbook" object, double click on it to open up its' code sheet and just paste Scott's code (or my simplification) in there. Drop down the "Procedure" list to see the other "Events" that are available. These can come in handy ar other times. Each worksheet has a code sheet too with corresponding "Event" procedures available. You might want to investigate them also.
excellent, thanks for the VB primer, i've never really had the time to dive into VB programming. : ok, i can get it to work if i run it as a macro, but i'm unable to run it automatically. i couldn't get it to work when i pasted into the module for the workbook.

Posted by darren on January 02, 2002 8:22 AM

I've got this working, however i would like to run the statement as BeforeSave rather than BeforeClose...when i edit the statement to BeforeSave i get a compile error, why?