How to Automatically Enter the User that Edited a Cell Reference

1

In excel, I need to know who the last user was that edited a particular cell with their name to populate?

Just watched your video on Time and Date automation with VBA and thought this was also possible?

Answer
Discuss

Discussion

Hi there! Glad you saw the video :) Did you want the user's name to fill into a cell like the date/time did or did you want it to be saved through another process or in another location?
don (rep: 1715) Jun 26, '18 at 5:35 pm
Thanks for quick response!! I was hoping to have the user's name update in Column Q everytime a cell was changed in columns a2:J1500. So if for example line 7 is altered in that range, the users name would populate across in this example Q7?
Sroncey21 (rep: 62) Jun 26, '18 at 6:37 pm
you're welcome :) Did you try my answer? I will add the macro that you are talking about with that range. Look to my Answer below here and let me know how that works.
don (rep: 1715) Jun 26, '18 at 6:57 pm
Ah yes, ok, I wasn't paying attention, it's because the timestamp is entered inside the table instead of just outside of it. The solution is to put the timestamps outside of column J or to use Application.EnableEvents. I updated the code to fix that issue by useing the EnableEvents feature.
don (rep: 1715) Jun 26, '18 at 7:19 pm
It worked! Thank you so much!! You are the man!!
Sroncey21 (rep: 62) Jun 26, '18 at 7:39 pm
You're very welcome! If you have any more questions, someone is always around, so feel free to ask :)
don (rep: 1715) Jun 26, '18 at 7:56 pm
So, I tried to protect my worksheet and then decided to scrap that idea because it messed with my macro. But now none will return a value in (E,F, or Q) - It does not error out or anything it just doesn't populate anything in those cell references anymore? I broke it already. I hope you can help : (
Sroncey21 (rep: 62) Jun 26, '18 at 8:30 pm
Ask a new question and upload a sample file. That's the easiest way to figure out the issue. It's most likely a tiny thing anyway.
don (rep: 1715) Jun 26, '18 at 8:40 pm
Add to Discussion

Answers

0
Selected Answer

If you want to get the user's name, as listed in Excel, you use this:

Application.UserName

To add it to the macro for the Automatic Timestamping, you could put this code at the bottom of the macro, before End Sub

'Update cell with username - 7 = Column G
Cells(Target.Row, 7).Value = Application.UserName

Change the 7 to the number of the column where you want the user name to go; 1 is A; 2 is B; and so on.

Update

Full macro code based on your comment:

Private Sub Worksheet_Change(ByVal Target As Range)
'Timestamp Data
'   TeachExcel.com

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

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

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

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

'Stop events from running
Application.EnableEvents = False

'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

'Update cell with username - 1 = Column A; 2 = Column B; etc.
Cells(Target.Row, 17).Value = Application.UserName

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

Remember to put this code into the worksheet that you want to use it on.

Also, test this code on a sample sheet first to make sure it works like you want.

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login