Excel VBA to count the ADDITIONAL comments added to a cell

[simora]

I am currently adding additional comments to a cell.

Is it possible to count the additional comments added to the cell ?
i.e : How many different comments are added, and show that running value in the comments themselves.
So the first comment =1 , & the next comment is Comment 2 etc... etc...

This is part of my current comments code which adds additional comments if there is a cell change;

   With Target.Offset(0, 1)
                Set x = .Comment
                    If x Is Nothing Then
                             .AddComment.Text Text:=Chr(10) & "$" & Target.Value & Chr(10) & "  " & strDate & ":" & " -  "  & " === " & vbCrLf
                       Else
                             .Comment.Text .Comment.Text & vbLf & "$" & Target.Value & Chr(10) & "  " & strDate & ":" & " -  "  & " === " & vbCrLf
                     End If   
    End With
 

[simora]

[QUOTE=simora;356074]I am currently adding additional comments to a cell.

As a workaround, what I did was to store the cell changes in another column and add that cell value to the comments in the Else portion of the code like:

Target.Offset(, 2).Value = Target.Offset(, 2).Value + 1
newCount = Target.Offset(0, 2).Value

Then I referenced & newCount in the Comments.
Not the most eloquent solution, but it works for me.

[p45cal]

try replacing:

If x Is Nothing Then
  .AddComment.Text Text:=Chr(10) & "$" & Target.Value & Chr(10) & "  " & strDate & ":" & " -  " & " === " & vbCrLf
Else
  .Comment.Text .Comment.Text & vbLf & "$" & Target.Value & Chr(10) & "  " & strDate & ":" & " -  " & " === " & vbCrLf
End If

with:

If x Is Nothing Then
  .AddComment.Text Text:=Chr(10) & "1. $" & Target.Value & Chr(10) & "  " & strDate & ":" & " -  " & " === " & vbCrLf
Else
  .Comment.Text .Comment.Text & vbLf & UBound(Split(.Comment.Text, vbCrLf)) + 1 & ". $" & Target.Value & Chr(10) & "  " & strDate & ":" & " -  " & " === " & vbCrLf
End If

[simora]

Thanks p45cal :

That numbers each entry in the Comments. Great.