When I set up a database I always include a last change date that auto
updates every time any field in the record changes. Does anyone have a
suggestion for how to achieve a similar function in a spreadsheet. i.e.
put the current date/time in a field in the row whenever anything in the
row changes?


--
GGG
------------------------------------------------------------------------
GGG's Profile: http://www.excelforum.com/member.php...o&userid=29220
View this thread: http://www.excelforum.com/showthread...hreadid=489447

GGG,

For example, to put the last change date into a column named "LastChangedColumn", copy the code
below, right click on the sheet tab, select "View Code", and paste the code into the window that
appears. You can change the A1:H1000 to a named range, a dynamic range, or any other range that you
want....

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range

If Not Intersect(Target, Range("A1:H1000")) Is Nothing Then
Application.EnableEvents = False
For Each myCell In Intersect(Target, Range("A1:H1000"))
Cells(myCell.Row, Range("LastChangedColumn").Column).Value = Date
Next
End If
Application.EnableEvents = True

End Sub


"GGG" wrote in message
...

When I set up a database I always include a last change date that auto
updates every time any field in the record changes. Does anyone have a
suggestion for how to achieve a similar function in a spreadsheet. i.e.
put the current date/time in a field in the row whenever anything in the
row changes?


--
GGG
------------------------------------------------------------------------
GGG's Profile: http://www.excelforum.com/member.php...o&userid=29220
View this thread: http://www.excelforum.com/showthread...hreadid=489447

Awsome Bernie - does exactly what I was looking for and I learned
something new!

Thanks, GGG


--
GGG
------------------------------------------------------------------------
GGG's Profile: http://www.excelforum.com/member.php...o&userid=29220
View this thread: http://www.excelforum.com/showthread...hreadid=489447

One quirk - when the scrip is enabled it disables the "undo"
functionality. Any ideas? If I disable the macro, the functionality
comes back

At least I can keep track of _when_ I made an error! Just can't undo
it. (Excel 2002 SP3)


--
GGG
------------------------------------------------------------------------
GGG's Profile: http://www.excelforum.com/member.php...o&userid=29220
View this thread: http://www.excelforum.com/showthread...hreadid=489447

That's an unfortunate side effect of using event code. If you want to be able to 'undo', you would
need to store the values ion a record sheet (using the change event) and have another macro that
would allow you to transfer the stored value back to the data sheet. Not too hard, just need
different code and another sheet.

If you are interested in doing that, post back.

HTH,
Bernie
MS Excel MVP


"GGG" wrote in message
...

One quirk - when the scrip is enabled it disables the "undo"
functionality. Any ideas? If I disable the macro, the functionality
comes back

At least I can keep track of _when_ I made an error! Just can't undo
it. (Excel 2002 SP3)


--
GGG
------------------------------------------------------------------------
GGG's Profile: http://www.excelforum.com/member.php...o&userid=29220
View this thread: http://www.excelforum.com/showthread...hreadid=489447