Automatically Insert PC or Excel Username of the Last User to Update a Cell in a Worksheet.

[Kabasa007]

Hello all, I am a first time user of this forum. My question is as follows: I have a data entry table from Column A to Column Q, then I have this VBA code below.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myTableRange As Range
Dim myDateTimeRange As Range
Dim myUpdatedRange As Range

'Your data table range
Set myTableRange = Range("A2:P1000000")

'Check if the changed cell is in the data tabe or not.
If Intersect(Target, myTableRange) Is Nothing Then Exit Sub

'Stop events from running
Application.EnableEvents = False

'Column for the date/time
Set myDateTimeRange = Range("S" & Target.Row)
'Column for last updated date/time
Set myUpdatedRange = Range("T" & Target.Row)

'Determine if the input date/time should change
If myDateTimeRange.Value = "" Then

    myDateTimeRange.Value = Now

End If

'Update the updated date/time value
myUpdatedRange.Value = Now

'Turn events back on
Application.EnableEvents = True
End Sub

The code creates an automatic date and time stamp in columns S and T when a new entry is first made in any cell in of a particular row in the worksheet, and when any entry on that row is updated or changed. Could someone help with editing the VBA code to include in column U

an automatic records the username of the last person to update any of the cells in columns A to Q?

Thank you all.

 

[RoryA]

You could use:

myUpdatedRange.Offset(, 1).value = environ("username")

to get the windows user name, or use Application.Username if you want the name shown in Excel Options.

 

[Kabasa007]

You could use:

myUpdatedRange.Offset(, 1).value = environ("username")

to get the windows user name, or use Application.Username if you want the name shown in Excel Options.

Thanks Rory, I am pretty very new to VBA coding, so I really don't know where to insert the line of code you shared in the initial one i posted here. Could you help with editing the original one directly?

 

[RoryA]

Put it after this line:

myUpdatedRange.Value = Now

 

[Kabasa007]

Put it after this line:

myUpdatedRange.Value = Now

Thanks Rory, It worked perfectly as i wanted!

I used the

Application.Username

instead.

Now I would like to combine this edited VBA code with another VBA code that immediately locks the cells in $A$2:$P$1000000 after new data is entered into those cells. I don't want other users to be able to edit or change whatever data they have entered into the worksheet.

See the new code below.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Sheet2.Unprotect "123"

If VBA.IsEmpty(Target) Then
    Target.Locked = False
    Else
    Target.Locked = True
End If

Sheet2.Protect "123"


End Sub

Thanks once again

 

[OlgaB]

Hi @Kabasa007 and @RoryA - thank you for this thread.
The solution works wondefuly.
However I have an issue when instead of updating the cells one by one the user copies value in, or just drags the previous value down.
Then the macros updates dates/user only for the first row in the changed range (in the attached example yes from the first row was dragged down)
Is there a way to fix it somehow?
Thanks a lot,
Olga.