Inserting Date/Time of Last Update

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lionel B. Dyck - 14 Apr 2007 03:27 GMT

Is there a way to insert a date and time in a cell(s) that will only be updated
if there is a change to the worksheet. I'm looking to have the date/time of the
last update so that it is easy to tell how current the worksheet is.

Thanks

Signature

Lionel B. Dyck <><
AIM ID: lbdyck Yahoo IM: lbdyck
Homepage http://www.lbdsoftware.com/
Blog: http://randommgmt.blogspot.com/

Reply to this Message

Nick Hodge - 14 Apr 2007 09:47 GMT

Lionel

You could put some code in a worksheet_Change() event like so (uses A1)

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

To implement right click the sheet tab and select view code and paste in
window and save

Signature

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
web: www.nickhodge.co.uk
blog: www.nickhodge.co.uk/blog/

FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007
www.officeusergroup.co.uk

> Is there a way to insert a date and time in a cell(s) that will only be
> updated if there is a change to the worksheet. I'm looking to have the
> date/time of the last update so that it is easy to tell how current the
> worksheet is.
>
> Thanks

Reply to this Message

davesexcel - 14 Apr 2007 12:34 GMT

You could place a code in th before save event,
this is in the workbook module

Code:
--------------------
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Range("A1") = _
"=TEXT(TODAY(),""d-mmmm-yyyy"") & "" "" & TEXT(NOW(),""h:mm AM/PM"")"

End Sub

--------------------

Signature

davesexcel

Reply to this Message

Lionel B. Dyck - 14 Apr 2007 19:48 GMT

Thank you for both suggestions - I'll give them a try.

Much appreciated

Signature

Lionel B. Dyck <><
AIM ID: lbdyck Yahoo IM: lbdyck
Homepage http://www.lbdsoftware.com/
Blog: http://randommgmt.blogspot.com/

Reply to this Message

Rate this thread: