Set a cell comment using VBA?

[AChimpNamedCornelius]

I am stumped by this, and am not sure if this even can be done. The documentation is contradictory.

I want to set the comment text of a cell using VBA.

I tried:

Sheet1.Range("D6").Comment.Text = "Hello"

with and without Set in front of it. I get the error "Assignment to constant not permitted" (compile time error)


And this:

Sheet1.Range("D6").Comment = "Hello"

yields a run time error of "Object doesn't support this property or method."

Assigning the cell to a variable dimmed as a Range doesn't get around it either.


Now, from the documentation for .Text:

Text Property


Returns or sets the text for the specified object. Read-only String for the Range object, read/write String for all other objects.

But, also from help:


Comment Property Example

This example sets the comment for scenario one on Sheet1.

Worksheets("Sheet1").Scenarios(1).Comment = _
"Worst case July 1993 sales"


So what am I doing wrong? I am not familiar with the .Scenarios member object, but why would one need to use it to adjust the comment of a cell or range of cells? If that is the only way, then how do I use .Scenarios to set the comment of, say, cell "D6"?

Thanks for any help anyone can give me.

 

[AChimpNamedCornelius]

I think the issue was that comments appear to be read only (MS programmer woke up on the wrong side of the bed that day), so clearcomment deleted it so it could be recreated. The other example did not do this.

 

[ahamedac]

Hi Jay,
I wanted to include a condition here in cell D6, that is to insert a comment when the value in D6 is "No", and the comment should be auto-inserted (with a specific which I can define or refer a cell) without I having to run a macro

 

[tbrock]

I'll leave the declarations out but I found this little nugget and it works for me in Excel 2013 on Windows 10.

For Each Comment In ActiveSheet.Comments
   With Comment
      curComment = .Text
      curComment = "***" & curComment
      .Text Text:=curComment
   End with
Next