PDA

View Full Version : Excel VBA to count the ADDITIONAL comments added to a cell



simora
02-03-2017, 01:55 PM
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
02-03-2017, 03:15 PM
[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
02-04-2017, 06:45 AM
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
02-04-2017, 12:36 PM
Thanks p45cal : (http://www.vbaexpress.com/forum/member.php?3494-p45cal)

That numbers each entry in the Comments. Great.