Need VBA to capture who and when

X

xrbbaker

I have looked at http://www.mcgimpsey.com/excel/timestamp.html and it gave me
some ideas and parts to use, but it isn't all that I need.

I would like to on a worksheet change event:

1) on the same row as the cell where the change was made, insert the
timestamp in column X
2) on the same row as the cell where the change was made, insert the userid
of the person who made the change in column y

Also, what happens to a change event when calc is off? Does XL queue up the
changes and then apply them one at a time once Calc is pressed?

Thanks for any help. -Russ
 
G

Gary''s Student

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
rw = Target.Row
Cells(rw, "X").Value = Now
Cells(rw, "Y").Value = Environ("UserName")
Application.EnableEvents = True
End Sub
 
X

xrbbaker

Gary,

This works great if someone is doing a simple edit. I have found two ways
where it fails.

1) If I copy a value and paste that value to 6 rows, it only creates the
audit information into the first destination row.
2) If I change a value from a data validation list it doesn't generate the
audit information.

How would I expand your code to cover these two possibilities as well?

thanks,

Russ
 
B

Bernie Deitrick

Russ,

I would suggest that you limit the action to a specific column (in this case, the A:A does that).
Change A:A to the column of interest.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each myCell In Target
rw = myCell.Row
Cells(rw, "X").Value = Now
Cells(rw, "Y").Value = Environ("UserName")
Next myCell
Application.EnableEvents = True
End Sub

Works for me, for all types of changes.


HTH,
Bernie
MS Excel MVP
 
B

Bernie Deitrick

For Each myCell In Target
should have been
For Each myCell In Intersect(Target, Range("A:A"))

Sorry,
Bernie
MS Excel MVP
 
X

xrbbaker

Bernie,

This is cool man. I already have a named range for the data I want audited.
Simply switching that range into your code and it is sweet! Thanks very
much!
 
B

Bernie Deitrick

This is cool man. I already have a named range for the data I want audited.
Simply switching that range into your code and it is sweet! Thanks very
much!
Click to expand...

You're welcome!

Bernie
 
D

Don Guillett

You should have stayed in the original thread.
Right click the sheet tab>view code>copy/paste
 
Top