How-To: Last change date on row

[GGG]

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?

[Bernie Deitrick]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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]

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

Thanks, GGG

[GGG]

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)

[Bernie Deitrick]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>