Recording user when changing Active Row

[Foynxter1]

Help please.
I found this code and have adjusted slightly to fit my needs.
My spreadsheet is called "Additional Charges" and I want to record any user that makes a change anywhere on the active row.

Code is :
'Set the user who modified the record
Dim ThisRow As Long ' make sure to declare all the variables and appropiate types
ThisRow = Target.Row
'protect Header row from any changes
If (ThisRow = 1) Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox "Header Row is Protected."
Exit Sub
End If
If Target.Column >= 1 And Target.Column <= 30 Then
MsgBox Target.Column
Dim sOld As String, sNew As String
sNew = Target.Value 'capture new value
With Application
.EnableEvents = False
.Undo
End With
sOld = Target.Value 'capture old value
MsgBox sOld & "Old"
Target.Value = sNew 'reset new value
MsgBox sNew & "New"
If sOld <> sNew Then
Range("E" & ThisRow).Value = Environ("username")
End If
Application.EnableEvents = True
End If

For some reason the code is only recognising column 1 (and recording if column 1 changes) but does not seem to recognise any other column.
I am probably missing something obvious but Any help much appreciated. Thanks

 

[My Aswer Is This]

Are you attempting to keep users from changing values in Row (1). And that is all?

 

[Foynxter1]

Are you attempting to keep users from changing values in Row (1). And that is all?

Thanks your reply - no, the main function is to record any changes to fields in the active row. At the moment, despite the code calling for target.column 1 through to 30, the change is only recorded when the change is made to column 1. No other column seems to currently register. Frustrating,
Any help much appreciated.
Thanks

 

[Foynxter1]

Thanks "My Answer Is This"
In addition to previous reply - I think the bottom part of the script is more relevant so have updated it below

If Target.Column >= 1 And Target.Column <= 30 Then
MsgBox Target.Column
Dim sOld As String, sNew As String
sNew = Target.Value 'capture new value
With Application
.EnableEvents = False
.Undo
End With
sOld = Target.Value 'capture old value
MsgBox sOld & "Old"
Target.Value = sNew 'reset new value
MsgBox sNew & "New"
If sOld <> sNew Then
Range("E" & ThisRow).Value = Environ("username")
End If
Application.EnableEvents = True
End If

Thanks

 

[My Aswer Is This]

So it sounds like to me you want to record when any change is may to any cell on your entire sheet.
And what do you want recorded? And where do you want this recorded actions stored?

So if John makes a change to cell G45 you want what recorded and where will this recorded data be placed.

What do you want?
The user names
The previous cell value
The changed cell value
The cell address
The time and date of the change

If your user makes a 5,000 cell changes a day this could be a lot of work for you computer to be doing.

 

[Foynxter1]

Hi, At this stage the only thing I need to record is 1) Username who changed data to Column E. This may be extended at sometime but for now it is enough to identify that a record has been changed from the initial input. I take your point that it could be a "busy" computer otherwise - which is why just need to record who has changed any value, in any column - without actually recording the the actual change - only the username.
Thanks for your help - I've been trying to resolve again today and it's now driving me insane.

 

[My Aswer Is This]

Are you now saying you only want username recorded when a change is made to column "K"?

And if so where do you want the UserName entered?

 

[Foynxter1]

Hi - what I am looking to do is record the username in column E where a change is made anywhere on the active row. Thanks

 

[My Aswer Is This]

Try this:
This is an auto sheet event scrip
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tabSelect View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Cells(Target.Row, "E").Value = Application.UserName
End Sub

 

[Foynxter1]

Thanks - but still no luck.
It seems that there is no easy answer so I will have to try find another way around it. Thanks for all your efforts anyway - much appreciated