MrExcel.com - Your One Stop for Excel Tips & Solutions

Last Update

Posted by Mark on January 10, 2002 3:23 PM

I'm trying to get a cell on my worksheet to display the last update time of the sheet.

Can anyone tell me how to accomplish this? The NOW() function and the TODAY() function are not what I'm looking for - I need the date/time of the last time the sheet (or a range of cells) were modified.

This is for Excel 2000 - thanks!

Mark.

Posted by Goneril on January 10, 2002 3:31 PM


Put these 2 procedures in the sheet's code module :-

Private Sub Worksheet_Calculate()
Range("A1") = Now()
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Range("A1") = Now()
End Sub


Posted by Mark on January 10, 2002 4:13 PM


I must be stupid - I did as you instructed, and also found a few other procedures similar to yours (on this page - further down) but I don't see any result!?

What the...???

Am I supposed to compile these some how? Sorry - I'm not a VB guy. I'm a software engineer - but don't play with MS products.

Thanks much in advance!!!

Mark.

Posted by on January 10, 2002 4:23 PM

I even figured out to compile them - still, I see nothing in any of my three sheets!

What's the problem here...

I'm on AIM <Luv2CatchBass>

Mark


Posted by Goneril on January 10, 2002 4:54 PM

Copy the two procedures
Right click on the sheet tab
Select View Code
Paste the procedures onto the window on the right-hand side

Posted by Mark on January 10, 2002 5:04 PM

I did that - I see nothing on my sheet - the code is there - nothing in A1.

Mark.

Posted by Goneril on January 10, 2002 7:09 PM


Make sure the code is in the Sheet's code module, not in a normal code module.

Also, for a date/time to appear in A1, an input from the keyboard has to be made to the sheet or a formula(or link) has to be updated/calculated.

Posted by Mark on January 11, 2002 9:37 AM

Also, for a date/time to appear in A1, an input from the keyboard has to be made to the sheet or a formula(or link) has to be updated/calculated.

--- Just for the record, the problem was with my Macro Security Settings - It was High - and that's no good. (well - it's good, but it prevented your code from running on my machine!) I set it to Med, restarted Excel and it all works as advertised... So next time you can tell the person it will work if the macros securtiy setting is NOT set to High...

Thanks for the code though!

Mark.