gztimmy
10-19-2016, 01:07 PM
So I wanted a way to track changes by a user to an excel worksheet. I didn't like the automated way that excel does it and searched and found a vba code on this site.
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 was " & preValue & Chr(10) & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " & Environ("UserName")
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
My issue is I would like the comments to stay and not be overwritten so it tracks all changes. I tried removing the "target.clearcomments" line but it gave me debug error. I'm also not very good at VBA so any help you guys could give me would be greatly appreciated.
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 was " & preValue & Chr(10) & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " & Environ("UserName")
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
My issue is I would like the comments to stay and not be overwritten so it tracks all changes. I tried removing the "target.clearcomments" line but it gave me debug error. I'm also not very good at VBA so any help you guys could give me would be greatly appreciated.