Audit - add date and user to cell when row contents change

[stubbsj]

Hi -
On my worksheet, I have 2 columns "Modified By" and " Modified Date". Rather then rely on the user to enter these values, I would like to change the values automatically anytime a user changes any value within a range of cells on a given row (i.e., any change to the columns A, B, F, G, H on a given row).

Col A Col B Col C Col D Col E Col F Col G Col H
fName lName Full Name Modified By Modified Date Address City State
John Doe Doe, John 123 Main St Anytown CA

Any help will be greatly appreciated.

Thanks in Advance,
Jim

[Spencer101]

Hi,

This is adapted from something similar I have set up.
Not sure how to get the user name in there, but at least you're half way done.

Hope it's of help.

Timestamp.xlsm

[stubbsj]

Hi Spencer -
When I change a cell in Timestamp, it does update the cell with the date but then Excel hangs and then bombs

Capture1.JPG

[StevenM]

The problem is you are changing the worksheet with Worksheet_Change, and so it calls itself again because this worksheet was just changed, which then it then changes it again and again ... etc.

I tried:

Please Login or Register  to view this content.

But ran into the same problem.

[Spencer101]

How odd... works perfectly well on mine.

I've added seconds into the time on the attached just to show there's no lag in its updating.
You can also tell it does not hang..

Sorry, by no means a VBA expert so no idea how to advise you further...

TimestampScreenDump.JPG

[StevenM]

Call me slow-witted, but how about:

Please Login or Register  to view this content.

Change "G" and "H" as needed.

[stubbsj]

Hi Steven -
Thanks for the reply. When I run this code, it places the user name correctly but not place the date (blank). Also, it still hangs in the next loop. I put a Msgbox(i) to see what was happening. The MsgBox keeps going on and on with a "1" for i.

Jim

[StevenM]

For what it is worth.

Please Login or Register  to view this content.

Would be the correct syntax.

But if you only change one cell, or cells, in one row, it will always be one.

When I run this code, it places the user name correctly but not place the date (blank). Also, it still hangs in the next loop.

When I cut and pasted the code back into the Sheet module, I ran into the same problem. I fiddled around, and it started working again. I don't know what caused the problem, and I don't know what I did to make it work again.

Have you formatted the cells in column "H" as a date?

[stubbsj]

Hi Steven -
Yes I did format to Date. I've been fooling around and it appears it keeps calling the function over and over. This latest iteration I tried it by using the date as a string. No help.

Here is my code
Private Sub Worksheet_Change(ByVal Target As Range)
' Exit Sub

Dim i As Long, dte As Date, datetime As Date
Dim sDate As String, sName As String
sName = Application.UserName
' sDate = Format(Now(), "[$-409]dddd, mmmm dd, yyyy @ h:mm:ss AM/PM")
sDate = Format(Now(), "mm/dd/yyyy")
MsgBox (sDate)
dte = Date
datetime = Now()

'Target is a Range so will always have a Row and Column (even if both are 1)
For i = 1 To Target.Rows.Count
MsgBox (i & vbCrLf & Target.Rows.Count & vbCrLf & sName & vbCrLf & sDate)
If Cells(Target(i, 1).row, "AA") <> sName _
Or Cells(Target(i, 1).row, "AB") <> sDate Then
Cells(Target(i, 1).row, "AA") = sName
Cells(Target(i, 1).row, "AB") = sDate
End If
Next i
End Sub

[StevenM]

Try this:

Please Login or Register  to view this content.

As always, change "G" & "H" as needed.

[stubbsj]

That worked. Thanks Steven for all your help.