| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel screen capture to capture cells and row and column headings | Excel Discussion (Misc queries) | |||
| KeyAscii won't capture the <Tab key | Excel Programming | |||
| Capture AutoFill... | Excel Programming | |||
| Capture a worksheet in VBA | Excel Programming | |||
| Up Capture | Excel Programming | |||
Linear Mode
