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

ahs004

Board Regular
Joined
Jul 18, 2011
Messages
83
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.
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

royUK

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
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?

Code:
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

Board Regular
Joined
Jul 18, 2011
Messages
83
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

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
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.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,271
Messages
5,600,650
Members
414,399
Latest member
Ninjee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top