CODEWITHSUNDEEP
excelvbacomments
Pete855217
Pete855217

Reputation: 1602

VBA Excel copy comment including formatting

I'm using Excel 2007, and trying to write a VBA subroutine that will copy cell comments (including formatting). Cell comments can contain basic text formatting (style eg. bold etc) and I can successfully copy the text, but can't find a way to bring the formatting with it.

I was hoping I could simply define a Comments object, then set it, but no go:

Sub TestCommentCopy()

Dim r As Range
Dim c As Comment
Set r = Selection

If (Not r.Areas(1).Comment Is Nothing) Then
    Set c = r.Areas(1).Comment
End If

'Set r(1, 2).Comment = c ' Object error
'   r(1, 2).Comment = c  'Object error
'   Set r(1,2).Comment = c ' Object error
r(1, 2).ClearComments ' Works
'   r(1, 2).AddComment c 'Does not work - requires text only

r(1, 2).AddComment c.Text 'Works, but only get plain text, no formatting

End Sub

Is there a way in Excel to copy one cell's comment to another, including the formatting, not just the text?

Upvotes: 1

Views: 7743

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

To copy formatted comments:

Sub Macro1()
    Range("E9").Copy
    Range("L3").PasteSpecial Paste:=xlPasteComments, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
End Sub

Upvotes: 3

Related Questions