Solved: Worksheet change event to populate timestamp

[Danny]

Greetings,
I have been trying to write a code that will automatically add the username and a timestamp in columns E and F when the user adds or changes a value in column C. I would think that this should be possible, but every time I try to account for a "bug" my solution seems to create another one... . If someone could show me where I am going wrong, I would appreciate it.

I can do this with the following code, but it does not work properly if the user adds more than 1 row at a time. If 5 rows of info are pasted it only populates E and F for the row of active cell instead of for each of the selected cells.

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then
Cells(Target.Row, 5).Value = Environ$("username")
Cells(Target.Row, 6).Value = Now
End If
End Sub[/VBA]

With this code I am able to have the username and timestamp populates correctly if the user pastes more than 1 row at a time. it will throw an error when you select a cell in column C, enter a value then hit tab, or if you hit enter it will populate the UN/date below the row that the data was entered. I am guessing this is because the selection is set after the change event takes place.

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Target.Column = 3 Then
For Each c In Application.Intersect(Columns(3), Selection)
Cells(c.Row, 5).Value = Environ$("username")
Cells(c.Row, 6).Value = Now
Next c
End If
End Sub[/VBA]

So then I tried setting the selection to a variable and using that in place of the selection range. But again if you select cell C4 and paste 5 rows of data, it only populates the UN/date in the previously selected range...
[VBA]Dim tstrng As Range
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Target.Column = 3 Then
For Each c In Application.Intersect(Columns(3), tstrng)
Cells(c.Row, 5).Value = Environ$("username")
Cells(c.Row, 6).Value = Now
Next c
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set tstrng = Selection
End Sub
[/VBA]

[GTO]

Greetings Danny,

Try your second one with these mods:
[vba]
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Application.EnableEvents = False
If Target.Column = 3 Then
For Each c In Application.Intersect(Columns(3), Target)
Cells(c.Row, 5).Value = Environ$("username")
Cells(c.Row, 6).Value = Now
Next c
End If
Application.EnableEvents = True
End Sub
[/vba]

Basically, kill recursing by shutting off enableevents for the moment, and use Target rather than Selection, as Target will be the range (cell or cells) that was/were changed, whereas Selection will be the cell Selected after hitting the tab/enter key.

Does that help?

Mark

[mikerickson]

[VBA]Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Column = 3 Then
Application.EnableEvents = False
With .Columns(1)
.Offset(0, 2).Value = Environ$("username")
.Offset(0, 3).Value = Now()
End With
Application.EnableEvents = True
End If
End With
End Sub[/VBA]

[Danny]

Both work great.
Thanks guys !!!