How-To: Last change date on row

  • Thread starter Thread starter GGG
  • Start date Start date
G

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?
 
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
 
Awsome Bernie - does exactly what I was looking for and I learned
something new!

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

At least I can keep track of _when_ I made an error! Just can't und
it. (Excel 2002 SP3
 
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
 
Back
Top