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 ?

Hi
use something like

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


-----Original Message-----
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 ?
.

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

-----Original Message-----
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 ?
.

"Neil" wrote in news:4b8901c48057$49229610
:

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 ?


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

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


In article ,
"Frank Kabel" wrote:

Hi
use something like

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


-----Original Message-----
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 ?
.

Hi JE
thanks - misread the OP's posting :-)

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

It's customary to provide a reference to the site:

http://www.contextures.com/xlcomments03.html

In article 05,
Ron wrote:

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

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

"JE McGimpsey" wrote in message
...
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


In article ,
"Frank Kabel" wrote:

Hi
use something like

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


-----Original Message-----
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 ?
.

JE McGimpsey wrote in news:jemcgimpsey-
:

It's customary to provide a reference to the site:

http://www.contextures.com/xlcomments03.html


Hand duly slapped.

Wasn't sure about "advertising", but it's a very good site none the less.