Is it possible?
 

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

Is it possible?
 

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
HIS


"Art®" wrote in message
...
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

Is it possible?
 

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

"Nick Hodge" wrote in message
...
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
HIS


"Art®" wrote in message
...
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

Is it possible?
 

Art

He/She could put this in a standard module. It checks the current state and
then toggles it the other way. You might want to put an 'EnableEvents=True'
into the workbook_close event too to make sure they don't forget to switch
it back on!

Sub ToggleEvents()
Dim bState As Boolean
bState = Application.EnableEvents
If bState Then
Application.EnableEvents = False
Exit Sub
End If
Application.EnableEvents = True
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Art®" wrote in message
...
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

"Nick Hodge" wrote in message
...
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
HIS


"Art®" wrote in message
...
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

Is it possible?
 

Nick,

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

Art

"Nick Hodge" wrote in message
...
Art

He/She could put this in a standard module. It checks the current state
and then toggles it the other way. You might want to put an
'EnableEvents=True' into the workbook_close event too to make sure they
don't forget to switch it back on!

Sub ToggleEvents()
Dim bState As Boolean
bState = Application.EnableEvents
If bState Then
Application.EnableEvents = False
Exit Sub
End If
Application.EnableEvents = True
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Art®" wrote in message
...
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

"Nick Hodge" wrote in message
...
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
HIS


"Art®" wrote in message
...
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