Need VBA to capture who and when
 

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

Need VBA to capture who and when
 

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
--
Gary''s Student - gsnu200721

Need VBA to capture who and when
 

Very cool Gary! I appreciate pithy code. Thanks very much for your help!!!

Russ

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200721

Need VBA to capture who and when
 

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

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200721

Need VBA to capture who and when
 

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


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

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200721

Need VBA to capture who and when
 

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

Sorry,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


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

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200721

Need VBA to capture who and when
 

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!

"Bernie Deitrick" wrote:


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

Sorry,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


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

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200721

Need VBA to capture who and when
 

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!

You're welcome!

Bernie

Need VBA to capture who and when
 

how do i put this code in excel to work?

anyone help pls
cheers

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200721

Need VBA to capture who and when
 

You should have stayed in the original thread.
Right click the sheet tabview codecopy/paste

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Haroon" wrote in message
...
how do i put this code in excel to work?

anyone help pls
cheers

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200721