Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Automatically Record The Last Username to Update a Shared Workbook in Excel

0
Hello all, my question is about this tutorial on the TeachExcel Youtube page, please see this link --> https://www.youtube.com/watch?v=gIPdx4-rqGg . Please, how do i go about editing the VBA macro to include in the worksheet a new field in column G that automatically records the username of the last person to update any of the cells in columns A, B, C and D? Thanks
Answer
Discuss

Answers

0
Selected Answer

 The code below will add the user's name in column G.

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

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

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

    '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("E" & Target.Row)
    'Column for last updated date/time
    Set myUpdatedRange = Range("F" & Target.Row)
    'Column for user executing last update
    Set myUserRange = Range("G" & Target.Row)

    'Determine if the input date/time should change
    If myDateTimeRange.Value = "" Then
        myDateTimeRange.Value = Now
    Else
        'Update the updated date/time value
        myUpdatedRange.Value = Now
    End If

    myUserRange.Value = Application.UserName

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

Please note that the code will write the time in column E, if that field is blank. It will write in column F if the E isn't blank. This means that column F will be over-written if more than one update is made. The code will write in column G every time and update is made, meaning any existing name will be over-written on every change.

Discuss

Discussion

Thanks Variatus, your modification on the code worked perfectly! Life saver!!!

Now I am trying to combine the modified code with another code that immediately locks the cells in A2:D50 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. I want to create a single VBA code for the worksheet that adds a timestamp for data entry and data update, adds name of the last user to update the sheet and now locks the cells after data entry is made.

See the new code i want to add to the exsisting code you just edited for me below.

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

Kabasa007 (rep: 2) Feb 19, '20 at 10:31 am
I'm glad your initial problem was solved. The new one is, well, a new one. It's hard to present it in the Discussion format, even harder to read, and totally against forum rules. Please ask your question in a new thread. Thank you.
Variatus (rep: 4889) Feb 19, '20 at 8:47 pm
Thanks Variatus, I have now added the new problem as a new thread.

You have been really helpful!
Kabasa007 (rep: 2) Feb 21, '20 at 8:40 am
Add to Discussion


Answer the Question

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