Hello Everyone,
I'm trying to capture the last five changes under comments in Excel via VBA code but I been unsuccessful. I noticed an old thread that had a similar request but has no details for who made the change and the date/time associated with the change.
What I would like is a similar outcome as the code below but I would like to capture the last 5 changes. The code below capture instantaneous changes only. If the same data is captured for the last 5 changes and is not deleted when the file is closed, that would be great.
Option Explicit
Public preValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Target.ClearComments
Target.AddComment.Text Text:="Previous Value is " & Chr(10) & preValue & Chr(10) & "Revised " & Chr(10) & Format(Date, "mm-dd-yyyy") & Chr(10) & Format(Time, "hh:mm AM/PM") & Chr(10) & "By " & Environ("UserName")
Target.Comment.Shape.Height = 70
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target = "" Then
preValue = "a blank"
Else: preValue = Target.Value
End If
End Sub
Thank you in advance for your help!
Tomi
Bookmarks