Macro to insert date/time/username into cell when any data on that row is change


Was wondering if his site has a macro or vba that willinsert the date, time, username into the last cell in the row when any cell in that row is changed.

I've doen some google searches and have seen a numbe rof variations of entering this type information, but usually it is to a static cell or sometimes int he tool bar or Excel header.

I'd appreciate any help ypi can provide.





Selected Answer

 The code below must be installed in the code sheet of the worksheet in which you want the action. Please take note of the comments in the code to customise the output to the particular needs of the worksheet on which it is deployed.

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 26 Jan 2018
    Dim R As Long                           ' row being changed
    Dim Cl As Long                          ' last column
    With Target
    Set Target = .Cells(1)                  ' ignore all but first changed cell
        R = .Row
    End With
    If R > 1 Then                           ' no record if row 1 is changed
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
        ' determine the last column as the right-most
        ' column with a caption in row 1
        Cl = Cells(1, Columns.Count).End(xlToLeft).Column
        ' The Format() function specifies no format.
        ' Therefore Excel will use the system default.
        Cells(R, Cl).Value = Format(Now()) & _
                             " (" & Application.UserName & ")"
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
    End If
End Sub


Thanks Variatus.  This performed exactly as I had hoped.  I appreciate your quick response!
DaveSp (rep: 2) Feb 1, '18 at 8:02 am
I'm glad my effort helped you. Please mark my answer as "accepted".
Variatus (rep: 4864) Feb 1, '18 at 11:02 am
Add to Discussion

Answer the Question

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