How to use the AddComment method of the range object

[Mister_joe]

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]

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]

You need to use myRange(1).Name.Name as well.

[Mister_joe]

Thanks a lot. I got it to work based on the following:

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

[Mister_joe]

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.