How To Automatically Update When Records Are Changed

Posted by Tim Morrow on December 04, 2001 3:30 AM

I have a spreadsheet that contains several rows of information. The first column is a 'last updated' column and contains the date that the row was last updated. This field is useful when someone looks at the spreadsheet each week they only have to examine those rows that have changed in the past week (or after the last time). This column is currently manually maintained. E.g. when someone changes a row they have to remember to go to the first column and update the date.

It occurs to me that this could be done automatically by use of a suitable macro or VB code. Unfortunately I'm not sure how this might best be done. I'd appreciate any suggestions on how I might go about it.

Posted by Bib on December 04, 2001 5:40 AM

Hello,

I have something that may suit your need. The idea is to write the current date in a cell each time the worksheet changes (using the Change event) :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim startRow As Integer ' the row where editing can start
Dim endRow As Integer ' the row where editing is not permitted anymore
Dim dateCol As Integer ' the column where to insert date

startRow = 2
endRow = 150
dateCol = 1

If Target.Row >= startRow And Target.Row &LT;= endRow Then
Cells(Target.Row, dateCol) = Now
End If
End Sub

Paste this code in a new worksheet and see what happens when you edit cells.

Hope it helps

Cheers

Posted by Tim Morrow on December 05, 2001 1:16 AM

startRow = 2 endRow = 150 dateCol = 1 If Target.Row >= startRow And Target.Row &LT;= endRow Then Cells(Target.Row, dateCol) = Now End If

Thank you kindly Bib!
It works perfectly, exactly what I wanted.
Very neat solution. Congratulations!
Cheers,
Tim