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 enable events 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: