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

Kabasa007

New Member
Joined
Feb 19, 2020
Messages
7
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.


VBA Code:
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
MRexcel.PNG
an automatic records the username of the last person to update any of the cells in columns A to Q?

Thank you all.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
You could use:

Code:
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.
 
Upvote 0
You could use:

Code:
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?
 
Upvote 0
Put it after this line:

Code:
myUpdatedRange.Value = Now
 
Upvote 0
Put it after this line:

Code:
myUpdatedRange.Value = Now


Thanks Rory, It worked perfectly as i wanted!

I used the
VBA Code:
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.

VBA Code:
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
 
Upvote 0
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.
 

Attachments

  • Capture.PNG
    Capture.PNG
    8.2 KB · Views: 8
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

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
Back
Top