Track changes by adding comment

[yaninja]

Good morning Mr Excel Forum,

Thanks in advance everybody for reading, helping, advicing.

i am looking to track changes in an excel sheet in a non-shared workbook. The old value should be written to the cell's comment on top of the previous tracking comment. It is supposed to not be modified by users. Unfortunately i receive an error runtime 91.

example of required comment :
Previous Value was azer
Revised 09-07-2017
By Yan
Previous Value was YRZX
Revised 03-07-2017
By Cin


Here is the code I added:

Option Explicit

Public VolDvaL As Variant
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") & VolDvaL
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

If Target.Comment.Text = "" Then
VolDvaL = ""
Else: VolDvaL = Target.Comments
End If


End Sub


I assume there is some information missing like " how to protect the comment from modification" and "how to add the previous value saved" but I am pretty sure somebody here will find how to insert this in the upper code

Many thanks in advance for your kind assistance.


Yaninja

 

[BerndKuerzinger]

[I]
'(PRIOR CODE)...

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
    
    If Target.Comment.Text = "" Then
    VolDvaL = ""
    Else: [COLOR=#ff0000]VolDvaL = Target.Comments[/COLOR]
    End If

End Sub
[/I]

I assume there is some information missing like " how to protect the comment from modification" and "how to add the previous value saved" but I am pretty sure somebody here will find how to insert this in the upper code

Many thanks in advance for your kind assistance.


Yaninja

Hi Yaninja,

Maybe I can help you first with the runtime error you run into. This is caused if you are in a cell that doesn't yet have a comment when executing the below:

 If Target.Comment.Text = "" Then

Try replacing this with the following:

If Not Target.Comment Is Nothing Then
    If Target.Comment.Text = "" Then
      VolDvaL = ""
    Else
      VolDvaL = Target.Comment.Text
    End If
End If

(Please not I also changed Target.Comments to Target.Comment.Text as I assume you want to have the existing comment text in this variable.)

There still are a couple of problems with the overall code as this does not lock the comments as you wanted yet. Also your comments will inflate every time someone double-clicks or enters the cell (even without changing the value) and comment text space is not unlimited so might not be really well suited for this kind of change tracking, but I don't know your specific case, so it might be workable.

Hope this helps.

Kind regards,
Bernd

 

[yaninja]

Thank you very much Bernd for your help.

I tried the code : The issue is that it copy all the change that have been applied to every cells!

For example:
a1 old value : abc
a1 new value = def

a2 old value = ghi
a2 new value =jkl

The comment in a2 after modification become as follow:
By yan Previous Value was ghi
Revised 09-07-2017
By yan Previous Value was def
Revised 09-07-2017
By yan Previous Value was abc
Revised 09-07-2017
By yan

The aim is that it track only the change into the specific cell

How could you handle this change Bernd ???

Thx in advance.

 

[BerndKuerzinger]

Hi Yaninja,
Sorry I was out for a couple of days. The issue you describe has to do with the fact that "VolDval" is not reset every time. If you change your formula for the Worksheet_Change event to the below it should work.

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") & Chr(10) & VolDvaL
VolDvaL = ""
End Sub

Let me know how it goes,
Bernd

---
Tumbs up / smiles much appreciated if a solution I proposed works for you.

 

[yaninja]

It works perfectly,

Thank you very much for your help Bernd!