Automatic Date Update

  • Thread starter Thread starter dwayne
  • Start date Start date
D

dwayne

I have a table set up as a database in Excel 97.

It has about 36 fields including a field for recording the
date a change is made to a record. Is there a formula I
can place in that cell which will automatically enter in
the current date when any field in the record is changed?

The date is in column B and the entries will change in
columns H:AJ.

Thanks
Dwayne
 
Hi Dwayne

you can't do this with a formula. You have to use VBA and process the
worksheet_change event: In your case you could use something like

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("H:AJ")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
Application.EnableEvents = False
With Me.Cells(Target.Row, "B")
.Value = Date
.NumberFormat = "mm/dd/yyyy"
End With

CleanUp:
Application.EnableEvents = True
End Sub


HTH
Frank
 
Back
Top