Add username to the same row of the person who make amendments

[ahs004]

Hi there,

I have a spreadsheet that I manage where i have 10 admins working on it but only one of them is working at any time.

The problem i am having is that the consistency is not all there and often there are duplications of the work. in order to be able to eliminate the inconsistency and duplication, I need to be able to log the username of the admin and the time of the last changes they made to a specific row.

for eg: is x has made a change in row no 24, i need their username printed in column AB24. and the date and time to AC24.

Hope you can help with this.

thank you.

 

[royUK]

This code will do that, but how do you want to trigger it. Maybe with the worksheet_change event? If so which column should be checked?

With ActiveSheet
.Cells(ActiveCell.Row, 28).Value = Environ("username")
.Cells(ActiveCell.Row, 29).Value = Format(Date, "dd mmm yyyy") & ", " & Format(Time, "hh:mm:ss")
End With

 

[ahs004]

Hi Roy,

I would want it to trigger all changes that is made to columns A to AA.

Also will this code work if I hide the column AB and AC and protect it so the users will not be able to see this info.

 

[royUK]

If you hide the columns it will make no difference to the code.

If you protect the sheet then you must make sure all input cells are unlocked, including the hidden columns.

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Count > 1 Then Exit Sub
        If Not Intersect(Target, Range(Cells(2, 1), Cells(Rows.Count, 27).End(xlUp))) Is Nothing Then
            Cells(.Row, 28).Value = Environ("username")
            Cells(.Row, 29).Value = Format(Date, "dd mmm yyyy") & ", " & Format(Time, "hh:mm:ss")
        End If
    End With
End Sub

Where to paste the code

 

[ahs004]

Thanks for this, it works

 

[royUK]

Glad it helped.