Get “users” who edited cells

[aircarrier]

Hello guys,

is it possible to get somewhere in the sheet or beside the cells that are password protected for edits, a name of the user who last made change.
I have locked sheet with passwords and I would like to get in one place names of the users when they make comments in the fields.

Hope this is possible

Thanks

 

[Joe4]

In VBA, you may be able to capture the username of the person making the update with code like:

Dim usr as String
usr = Environ("USERNAME")

 

[Logit]

Paste the following code in the ThisWorkbook module :

Option Explicit

Dim vOldVal 'Must be at top of module

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim bBold As Boolean


If Target.Cells.Count > 1 Then Exit Sub
If ActiveSheet.Name = "Pricing" Then Exit Sub

'On Error Resume Next

    With Application
         .ScreenUpdating = False
         .EnableEvents = False

    End With

    If IsEmpty(vOldVal) Then vOldVal = "Empty Cell"
    bBold = Target.HasFormula
        With Sheets("Tracker")
            '.Unprotect Password:="Secret"
                If .Range("A1") = vbNullString Then
                    .Range("A1:H1") = Array("Cell Changed", "Old Value", _
                        "New Value", "Old Formula", "New Formula", "Time of Change", "Date of Change", "User")
                End If

            With .Cells(.Rows.Count, 1).End(xlUp)(2, 1)
                  .Value = ActiveSheet.Name & " : " & Target.Address
                  .Offset(0, 1) = vOldVal
            With .Offset(0, 2)
              If bBold = True Then
                .ClearComments
                .AddComment.Text Text:= _
                     "NOTE :" & Chr(10) & "" & Chr(10) & _
                        "Bold values are the results of formulas"

              End If
                .Value = Target
                .Font.Bold = bBold
                
            End With
                .Offset(0, 3) = Time
                .Offset(0, 4) = Date
                .Offset(0, 5) = Application.UserName
            End With
            .Cells.Columns.AutoFit
            '.Protect Password:="Secret"
        End With

    vOldVal = vbNullString

    With Application
         .ScreenUpdating = True
         .EnableEvents = True
    End With
On Error GoTo 0

MsgBox "There was a change to this sheet !"
End Sub


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    vOldVal = Target
End Sub

Private Sub test()
    Application.EnableEvents = True
End Sub

Download example workbook at : Internxt Drive – Private & Secure Cloud Storage

 

[aircarrier]

Hope this makes it a bit cleared, basically it's simple logic what I want to do, but I'm not very familiar with VBA coding, but I will get it since I am not that noob.
Thanks

 

[Joe4]

I think just about any code that you use, whether you use the "Environ("USERNAME")" option, or the "Application.UserName" option is going to return their network ID, which is typically not a "friendly" name like "Becky Anderson"; it is usually the username that you typically log into your network with.

So if you want to return the "friendly" name like "Becky Anderson", you will probably need a lookup table that has two columns, where column 1 is the network ID and column 2 is the friendly name. then the VBA code would get the network ID using one of the two methods explained above, look up the friendly name from your lookup table, and populate the cell with that.

 

[Logit]

Generally speaking you won't be able to list the users by their name as most all computers are not set up that way.
In that case you'll need to establish a table that lists the users name as well as the computer's name. Then you'll need
to create a macro that searches for the computer name first and 'pulls' the user name from that list to be included
in the Comment Tracking List sheet, along with the department name and the date.

Another approach would be to edit each users computer to reflect their name for the computer name. Then you can
avoid creating the extra macro, etc.

The users name can be logged in red simply by formatting the column Font as red color.

(Joe4 ... sorry for the duplicate submissions. I was finishing my entry just after you posted.)

 

[Joe4]

(Joe4 ... sorry for the duplicate submissions. I was finishing my entry just after you posted.)

No worries - nothing wrong with reiterating the process. If two people suggest similar things, it emphasizes the fact that it probably is the way to go!

 

[aircarrier]

Generally speaking you won't be able to list the users by their name as most all computers are not set up that way.
In that case you'll need to establish a table that lists the users name as well as the computer's name. Then you'll need
to create a macro that searches for the computer name first and 'pulls' the user name from that list to be included
in the Comment Tracking List sheet, along with the department name and the date.

Another approach would be to edit each users computer to reflect their name for the computer name. Then you can
avoid creating the extra macro, etc.

The users name can be logged in red simply by formatting the column Font as red color.

(Joe4 ... sorry for the duplicate submissions. I was finishing my entry just after you posted.)

Does it help if everybody uses Office 365 for business and everybody is registered with their emails?

 

[Joe4]

Does it help if everybody uses Office 365 for business and everybody is registered with their emails?

I don't know if that makes any difference.

How exactly do users log in to your network?
Do you log in with a username of some sorts and a password?
Whatever username you log in with, that is what the VBA code will probably pull back. So if you want to convert it to a "friendly name", you will probably need to have a lookup table that does that.

 

[aircarrier]

I don't know if that makes any difference.

How exactly do users log in to your network?
Do you log in with a username of some sorts and a password?
Whatever username you log in with, that is what the VBA code will probably pull back. So if you want to convert it to a "friendly name", you will probably need to have a lookup table that does that.

Everybody is registered on office through their unique company email with surname.
I guess I can use that
But I will have to try to figure out how to run this macro that you have posted and adapt. I had problem opening it today even though I enabled macro
I will try and give you the update, hopefully I get it