Cell Comment

N

Neil

I want to add a comment from VBA to a cell, I have
recorded the addcomment part but it falls over fi there
is alreadya comment in place, how do I checkto see if the
cell already has a comment, then append the new comment
to the old Comment ?
 
F

Frank Kabel

Hi
use something like

with activecell
on error resume next
..comment.delete
on error goto 0
'insert your comment
end with
 
R

Rog

Neil, this should do what you want :

just pass it the cell you are interested in, and append
the returned string (if any) to your new comment



Function ReturnsCommentAndDeletes(ByRef p_rngCell As
Range) As String

Dim objComment As Comment

Set objComment = p_rngCell.Comment

ReturnsCommentAndDeletes = vbNullString

If Not objComment Is Nothing Then
ReturnsCommentAndDeletes = objComment.Text
objComment.Delete
End If

End Function


Rgds

Rog
 
R

Ron

I want to add a comment from VBA to a cell, I have
recorded the addcomment part but it falls over fi there
is alreadya comment in place, how do I checkto see if the
cell already has a comment, then append the new comment
to the old Comment ?
Click to expand...

Neil,

Got this from a website, may/maynot help you.

Sub CommentAddOrEditTNR()
'adds TimesNewRoman comment or positions
'cursor at end of existing comment text
Dim cmt As Comment
Set cmt = ActiveCell.Comment
If cmt Is Nothing Then
ActiveCell.AddComment text:=""
Set cmt = ActiveCell.Comment
With cmt.Shape.TextFrame.Characters.Font
.Name = "Times New Roman"
.Size = 11
.Bold = False
.ColorIndex = 0
End With
End If
SendKeys "%ie~"
End Sub
 
J

JE McGimpsey

Frank - this won't append the new comment to an old comment.

Instead try:

Public Sub AddOrAppendComment()
Const sMYCOMMENT As String = "My Comment"
Dim sExisting As String
With ActiveCell
If .Comment Is Nothing Then
.AddComment sMYCOMMENT
Else
sExisting = .Comment.Text
.Comment.Delete
.AddComment sExisting & " " & sMYCOMMENT
End If
End With
End Sub
 
D

Dana DeLouis

Here's a slight variation with AutoSize.

Public Sub AddOrAppendComment(s As String)
With ActiveCell
If .Comment Is Nothing Then
.AddComment s
Else
.Comment.Text (.Comment.Text & vbLf & s)
End If
.Comment.Shape.TextFrame.AutoSize = True
End With
End Sub

Dana DeLouis
 
Top