CODEWITHSUNDEEP
vbaexcel
mcha
mcha

Reputation: 2998

How can I automatically populate user name and update time when a cell is updated?

Say I have a sheet with 4 columns as the following:

value1 | value2 | updated by | update time
abc    |        | Employee1  | 18/12/2014 18:13
       | xyz    | Employee2  | 18/12/2014 18:13

I want the columns "updated by" and "update time" to be automatically updated if any of the cells in Value1 or Value2 has been updated.

The column updated by takes the name of the user that made the change, it can be the Windows user name or Excel username.

The column update time takes the time when any of the cells value1 or value2 has been updated.

I have no VBA knowledge; is there any way how to achieve this result? I have been searching online, I found out how to populate the update time column via a VBA procedure but the result is not exactly what is expected, and couldn't find how to pouplate the Updated By column.

The VBA code I found online to populate the time is the following:

Private Sub Worksheet_Change(ByVal Target As Range) 
    If Not Intersect(Target, Range("A2:A100")) Is Nothing Then 
        Target(1, 4) = Now 
        Target(1, 4).EntireColumn.AutoFit 
    End If 
End Sub

source: http://www.ozgrid.com/forum/showthread.php?t=24219

Upvotes: 0

Views: 13893

Answers (3)

Alexander Bell
Alexander Bell

Reputation: 7918

Following is the possible VBA solution to your problem:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Column = 1 Or Target.Column = 2 Then
        ThisRow = Target.Row
        If (ThisRow = 1) Then Exit Sub
        ' time stamp corresponding to cell's last update
        Range("D" & ThisRow).Value = Now
        ' Windows level UserName | Application level UserName
        Range("C" & ThisRow).Value = Environ("username") & "|" & Application.UserName
        Range("C:D").EntireColumn.AutoFit
    End If
End Sub

In order to protect Header Row from changes you can add code snippet shown below:

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
                 ThisRow = Target.Row
                 'protect Header row from any changes
                 If (ThisRow = 1) Then
                        Application.EnableEvents = False
                        Application.Undo
                        Application.EnableEvents = True
                        MsgBox "Header Row is Protected."
                        Exit Sub
                 End If
                 If Target.Column = 1 Or Target.Column = 2 Then
                        ' time stamp corresponding to cell's last update
                        Range("D" & ThisRow).Value = Now
                        ' Windows level UserName | Application level UserName
                        Range("C" & ThisRow).Value = Environ("username") & "|" & Application.UserName
                    Range("C:D").EntireColumn.AutoFit
                 End If
End Sub

Hope this will help. Best regards,

Upvotes: 1

silentsurfer
silentsurfer

Reputation: 2438

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A:B")) Is Nothing Then

    Cells(Target.Row, 3) = Now                   'Current time
    Cells(Target.Row, 4) = Application.UserName  'Excel User Name
    Cells(Target.Row, 5) = Environ("UserName")   'Windows User Name

    Range("C:E").EntireColumn.AutoFit
End If

End Sub

Upvotes: 0

mrbungle
mrbungle

Reputation: 1931

For the windows user name you could use

ActiveCell.Value = Environ("username")

What didn't work out for the time stamp?

Upvotes: 0

Related Questions