How do I automatically add a comment that contains the current date, time and
the user's name each time data is entered into a cell or modified? I have a
range of cells in an application that I want to keep track of by putting the
information in comment.
Thanks,
Emma
Hello Emma,
This macro will record the who made the change (logged on user name),
the time, the old value, and the new value when the cell's contents
change. Because this macro uses the Worksheet Change event, you must
copy this macro into each sheet's Change event you want to have this
capability.
'Writtem April 30, 2007
'Author: Leith Ross
'Summary: Place this code in the Worksheet_Change() event procedure
' Every time a cell's value is changed the date, time , old
value,
' new value, and the user are recorded in a comment.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewText As String
Dim NewVal As Variant
Dim OldText As String
Dim OldVal As Variant
Application.EnableEvents = False
NewVal = Target.Value
Application.Undo
OldVal = ActiveCell.Value
ActiveCell = NewVal
Application.EnableEvents = True
NewText = "On " & Now() & " cell changed from " & OldVal _
& " to " & NewVal & " by " & Environ("UserName")
If ActiveCell.Comment Is Nothing Then
ActiveCell.AddComment
End If
With ActiveCell.Comment
.Shape.TextFrame.AutoSize = True
OldText = .Text & vbLf
.Text Text:=OldText & NewText
End With
End Sub
Installing the Worksheet_Change() Event Macro
1. Copy the Macro code above using CTRL+C
2. Open Excel and Right Click the Sheet Tab the macro will run on.
3. Click on View Code in the pop up menu
4. Press CTRL+V to Paste the macro code
5. Press CTRL+S to Save the macro code in the workbook
6. Close the VBE and return to excel using ALT+Q
Sincerely,
Leith Ross
