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.)
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.
Thanks,
DaveSP
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 & ")"
Columns(Cl).AutoFit
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End If
End Sub