PDA

View Full Version : [SOLVED] How to use the AddComment method of the range object



Mister_joe
04-10-2014, 10:56 AM
Hello there,
I have a main range in a worksheet. I need to copy sections of this main range and paste in separate regions of the same worksheet. This way, I would have subsets of the main range. The sub ranges need to remember their parent range. So, I decided to store the name of the parent range in the comment property of the sub range. The comment should be invisible. However, I am having trouble using the AddComment method of the range object. See the code snippets below:


Selection.AddComment myRange(1).Name
Selection.Comment.Visible = False
Debug.Print Selection.Comment


The command Selection.AddComment myRange(1).Name brings up an error message that says "Invalid procedure call or argument"


Selection.NoteText myRange(1).Name
Debug.Print Selection.NoteText

The Debug.Print Selection.NoteText returns the expected comment; something like ='Sheet1'!$F$25:$I$130, but then I cannot make the comment invisible.


Could you please enlighten me on how to use the Addcomment method of the range object. I am using Excel 2007.

p45cal
04-10-2014, 01:07 PM
I think you can only add comments to one cell at a time.
However, you can copy comments to a multi-cell range in one go. Perhaps something along these lines:
Dim mycomment As Object
With Selection
With .Cells(1)
Set mycomment = .Comment
If Not mycomment Is Nothing Then .Comment.Delete
.AddComment "here a comment"
.Comment.Visible = False
.Copy
End With
.PasteSpecial Paste:=xlPasteComments, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End With

Aflatoon
04-10-2014, 01:46 PM
You need to use myRange(1).Name.Name as well.

Mister_joe
04-10-2014, 01:49 PM
Thanks a lot. I got it to work based on the following:


With Selection.addcomment
.text "Whatever"
.Visible = False
End With

Mister_joe
04-10-2014, 02:09 PM
It worked with:


With Selection.AddComment
.text myRange(1).Name.Name
.Visible = False
End With


There is only a single cell in the selection. It doesn't work when there are multiple cells in the selection.

Thanks for the assistance.