Automatically Update the Username After Each New Entry

[Magdoulin]

Hi guys,

I have project with Template sheet that will be copied with the same table structure from time to time
The end user will pick one of them and go to log his / her data
I need to specify column in the Template just to read the user GTI and log it automatically after each row update by new entry
I’ve written the below code but apparently it changes the value of the entire usernames column cells every time different user update any cell in Column B
I need it to be smart to change only the last updated row by this specific end user

Dim rw As Range
Sub forEachWs()
For Each rw In ActiveSheet.Rows
If ActiveSheet.Cells(rw.Row, 2).Value <> "" Then
ActiveSheet.Cells(rw.Row, 6).Value = Environ("UserName")
End if
Next rw
End Sub

On the other hand, this one doesn’t work the way I want it to do, I need the following, if the end user update A8 with some value, to update A9 with the GTI username automatically, like the cell underneath, obviously it updates the rest of the cells in the whole column with the GTI value, I need one cell only which is the one under the last cell update

Dim rw As Range
Sub forEachWs()
For Each rw In ActiveSheet.Rows
If ActiveSheet.Cells(rw.Row, 8).Value <> "" Then
ActiveSheet.Cells(rw.Row + 1, 8).Value = Environ("UserName")
End If
Next rw
End Sub

 

[Fluff]

Try

Sub forEachWs()
For Each rw In ActiveSheet.Rows
If ActiveSheet.Cells(rw.row, 8).Value <> "" Then
ActiveSheet.Cells(rw.row + 1, 8).Value = Environ("UserName")
[COLOR=#0000ff]Exit For[/COLOR]
End If
Next rw
End Sub

 

[Magdoulin]

Well, I get many issues now

1. For some reason, the code doesn’t trigger automatically when Cells(rw.Row , 8) change, you have to run the code manually to take effect
2. If I entered some value in H1 (1, 8), and in H3 (3, 8), when I run the code for whatever number of times, it doesn’t update except H2, even I’m locating the mouse curser over H3 or the cell underneath, H4.
3. For the below code

Dim rw As Range
Sub forEachWs()
For Each rw In ActiveSheet.Rows
If ActiveSheet.Cells(rw.Row, 2).Value <> "" Then
ActiveSheet.Cells(rw.Row, 6).Value = Environ("UserName")
End if
Next rw
End Sub

Again, it changes the value of all cells located in column 6, it doesn’t change the value of the last updated wrote as I wish

 

[Fluff]

That code has never run automatically unless you called it from an Event of some sort.
Also the code doesn't update H2, or any other cell in col H as you have change the code to write to col F.
If you explain what you are trying to achieve, I will see what I can do.

 

[Magdoulin]

Ok, to avoid the confusion, let me hint that I’ve posted two different codes in my original post, but, let us deal with this code first:

Dim rw As Range
Sub forEachWs()
For Each rw In ActiveSheet.Rows
If ActiveSheet.Cells(rw.Row, 2).Value <> "" Then
ActiveSheet.Cells(rw.Row, 6).Value = Environ("UserName")
End if
Next rw
End Sub

Now, what kind of modification should I apply on this code so if an end user updates for example B1, then F1 changes automatically to the username

And if another user updated for instance B3, then F3 updates automatically to the username of the second user and so on

So, I need it to be 1) triggered automatically, and, 2) to update the different username by each new entry, not to the whole entries

I'm sorry for the bothering

 

[Fluff]

Ok, try this

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
Application.EnableEvents = False
   If Target.Column = 2 Then
      Target.Offset(, 4).Value = Environ("Username")
   End If
Application.EnableEvents = True
End Sub

It needs to go in the sheet module.

 

[Magdoulin]

.

 

[Magdoulin]

Brilliant
But here’s my issue, because I thought about the event solution, and I left it for the following.
My project depends upon the concept that there’s one template sheet, would be copied every day twice at least, so, updating the event every day two times at least for each new created sheet will be hectic.
Is there a work around? Taking in account that I’m using code to copy this template everyday, is there a way to embed the event somehow to be created with the new created sheet?

 

[Fluff]

If the code is in the template sheet, then when you copy the sheet, the code will automatically be copied as well.

 

[Magdoulin]

Yup, you're right, good to know
and I manage with the second VBA myself
Thank you, you were great help today