Date when I last change cell in a row?

[Guest]

Please, Help.
In my database, every row represents a separate client. I wonder if it is
possible to input the date when I last changed any value in a particular row.
This way I will know when exactly I last updated the information for client
in row 4, 5 etc.
I don't need the date when the document was last saved.
Please, let me know if it can be done in Excel.

Thank you,

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Row = 5 Then
Me.Range("H1").Value = Date
Me.Range("H1").NumberFormat = "dd-mmm-yyyy"
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

I have a similar need and this looked like it should work for me but I
couldn't get it to work perhaps you can tell me what I'm did incorrectly.

I inserted the code as indicated into the sheet's code area. Is there
anything else I have to do to get it to execute?

assumed Me.Range ("H1") was the indicated which column to put the date in
and chaged H1 to Z1 since column Z is where i need the date to go

 

[Dave Peterson]

Did you enable macros when you opened the workbook
Check tools|macro|security|security level
If it's set to not ask and not run macros, then you could have problems.

(Change it to medium to always prompt you (what version of excel are you
using?))

Bob's code looks for changes in row 5. Is that what you wanted?

You may want to look at how JE McGimpsey does it (just for comparison):
http://www.mcgimpsey.com/excel/timestamp.html

But it's difficult to guess.

 

[Guest]

Macros are enabled. I wasn't aware that as coded only row 5 was checked.
What I want to do is insert the current date in column Z of any row that
changes have been made in. Any Idea how to mod code to accomplish that; it
should be fairly obvious I don't. Thanks.

 

[Gord Dibben]

Dan

Private Sub Worksheet_Change(ByVal Target As Range)
'for any column or row with Z as receiver of date
On Error GoTo enditall
Application.EnableEvents = False
If Not IsEmpty(Cells(Target.Row, "Z")) Then Exit Sub
If Not IsEmpty(Target.Value) Then Cells(Target.Row, "Z") = Now
enditall:
Application.EnableEvents = True
End Sub


Gord Dibben Excel MVP

 

[Dave Peterson]

Just to add to Gord's response.

His code says to not update column Z if it already has something in it. So if
you change A1 today (then Z1 will be populated with that moment's date/time.)

But if you change B1 tomorrow (and haven't touched Z1), then Z1 will remain with
the initial date.

This is the line that says first change wins:
If Not IsEmpty(Cells(Target.Row, "Z")) Then Exit Sub

If you want every change to overwrite the date/time in column Z, just delete
that line.

 

[Guest]

Thanks, code you provided does exactly what I needed.
Dan Morton

 

[Guest]

Thanks for your help with change suggested by Dave the code you suggested
does exactly what I needed.
Dan