Is it possible?

[Art®]

Is it possible to create a spreadsheet so that everytime someone edits any
cell in a particular row, a date is either inserted (or edited) in a column
in that row with the current date of the edit?

Example: before edit
Date Data1 Data2 Data3
09/04/04 abcde 12345 xyz321


Example: after edit
Date Data1 Data2 Data3
09/20/04 abcde 54321 xyz321

Art
West Palm Beach

 

[Nick Hodge]

Art

You could use a worksheet_change event. Something like...

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Columns("A:A")) Is Nothing Then
MsgBox "You cannot enter data in column A", vbOKOnly
Target.Value = Date
Application.EnableEvents = True
Exit Sub
End If
Range(Cells(Target.Row, 1), Cells(Target.Row, 1)).Value = Date
Application.EnableEvents = True
End Sub

This will fire when anything is changed in the sheet, therefore it is really
only practical for data entry. It would need far more refining if lots of
deleting of rows, etc was to take place.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Art®]

Nick,

Thanks a million for the smple macro. It's just what the doctor ordered.

One further question: is it possible to temporarily interrupt the macro so
the user can manually edit a cell in Column A, and then re-enable the macro
once the edit is finished?

Art

 

[Art®]

Nick,

Thanks again for your help and quick response. Both macros work great and
the worksheet now does exactly what was wanted.

Art