Track and display multiple changes by different users as comment

[bcs01]

Hey everyone,

I am working on a VBA Code that is supposed to track the last 5 changes made to cells and displaying them as comments (without having to share the workbook).
I already found a really good Code that displays the last 5 changes made to a cell.

Nevertheless if I open the file again after having saved my changes, the previous comment content is displayed at first, but deleted completely if I edit the cell.

Is there a way Excel always displays the last 5 changes made to the cell without deleting the complete comment content in case someone else makes a change ?

Furthermore, I would like to apply the rule to many cells within one column. For example P9-P25 and Q8-Q38.

So far I got the following Code which is valid for cell P9:

Option Explicit
Dim preValue(5) As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$P$9" Then

preValue(5) = preValue(4)
preValue(4) = preValue(3)
preValue(3) = preValue(2)
preValue(2) = preValue(1)
preValue(1) = preValue(0)

Target.ClearComments
Target.AddComment.Text Text:="Previous Values are " & Chr(10) & preValue(1) & Chr(10) & preValue(2) & Chr(10) & preValue(3) & Chr(10) & preValue(4) & Chr(10) & preValue(5)
Target.Comment.Shape.Height = 100
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$P$9" Then
If Target = "" Then
preValue(0) = "a blank"
Else
preValue(0) = Target.Value
End If
End If

End Sub

Any help is very much appreciated.
Thanks a lot,

Jan

[Ringhal]

Replace:
Target.ClearComments
Target.AddComment.Text Text:="Previous Values are " & Chr(10) & preValue(1) & Chr(10) & preValue(2) & Chr(10) & preValue(3) & Chr(10) & preValue(4) & Chr(10) & preValue(5)
Target.Comment.Shape.Height = 100 With:
Target.Comment.Text Text:="Previous Values are " & Chr(10) & preValue(1) & Chr(10) & _
preValue(2) & Chr(10) & preValue(3) & Chr(10) & preValue(4) & Chr(10) & preValue(5)
Target.Comment.Shape.Height = 100

[SamT]

Jan,

I am working on a VBA Code that is supposed to track the last 5 changes made to cells and displaying them as comments (without having to share the workbook).
I already found a really good Code that displays the last 5 changes made to a cell.

Nevertheless if I open the file again after having saved my changes, the previous comment content is displayed at first, but deleted completely if I edit the cell.

If you only edit the Cells Contents, the Comment doesn't change. However if you Set the Cell = Another Cell, it will keep the comment, or lack thereof, of the Other Cell.
Set Range1 = Range2 ' =Range1 gets Range2's comments

Is there a way Excel always displays the last 5 changes made to the cell without deleting the complete comment content in case someone else makes a change ?

You would have to use VBA's menu code to prevent Comment or Cell deleting.

Furthermore, I would like to apply the rule to many cells within one column. For example P9-P25 and Q8-Q38.

Dim TrackChanges As Range
Set TrackChanges = Range("P2:P25,Q8:Q39")
If Not Intersect(Target, TrackChanges) Then Exit Sub
Set TrackChanges = Intersect(TrackChanges, Target)
For Each Cel in TrackChanges
'Do stuff to Cel Comments.

End Sub

Dealing with Comments. Handles any number of changes up to TrackingLimit - 1.
Const TrackingLimit As Long = 6 '1 greater than # of changes to track
Private Sub SamT()
Dim CommentLines As Variant
Dim NewLine As String
Dim NewComment As String
Dim Start As Long
'Init Start
Start = 0 'Used when changes already tracked is not yet 5


CommentLines = Split(Cel.Comment.Text, Chr(10))
NewLine = Cel.Text

Redim Preserve(CommentLines, Ubound(CommentLine) + 1
CommentLines(Ubound(CommentLines) = NewLine
If Ubound(CommentLine) = TrackingLimit Then Start = 1 'Skip the oldest if already 5 changes tracked.

Cel.Comment.Delete
For i = Start To (Ubound(CommentLines)
NewComment = NewComment & CommentLines(i) & Chr(10)
Next i
'Add NewComment to Cel

The above was written off the top of my head without looking at the helps and probably contains errors. :devil2: