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...:dunno . 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.

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

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.

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

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... :banghead:
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

[GTO]

Greetings Danny,

Try your second one with these mods:

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


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]

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

[Danny]

Both work great.
Thanks guys !!! :bow: