Set a cell comment using VBA?

AChimpNamedCornelius

Board Regular
Joined
Aug 22, 2002
Messages
91
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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Hi,

Use .AddComment rather than just .Comment

Try the following...

<pre>Sub test()
Dim rng As Range

Set rng = Sheet1.Range("D6")

rng.ClearComments
rng.AddComment "Hello"


End Sub</pre>
 
Upvote 0
Sheet1.Range("D6").AddComment ("Hello")
Sheet1.Range("D6").AddComment = "Hello"
Sheet1.Range("D6").AddComment "Hello"

All give "Run-time Error 1004: Application-defined or object-defined error"

Yes, the cell is unlocked.


Are there any other permutations? According to the documentation, the first form should work, AddComment("the text")

"Text" should be a variant, so I tried this:

Dim PissPoorDesign As Variant
PissPoorDesign = "Hello"
Sheet1.Range("D6").AddComment (PissPoorDesign)

But that (and above variations) gives the same 1004 error.



Any more ideas anyone of what could be wrong?
 
Upvote 0
Dim rrr As Range

Set rrr = Sheet1.Range("D6")
rrr.AddComment = "Hello

By the way, this gets the same error too, along with similar combinations, i.e. rrr.AddComment("Hello").

I also have to use "Set" in front of the rrr assignment or else I get a different error.
 
Upvote 0
Run this from a Sheet module, hot-key or Form Button.

Sub myComment()
Dim rng As Range

Set rng = Sheet1.Range("B23")
rng.ClearComments
'Direct from coded text!
'rng.AddComment "This is a test comment!"

'From cell text!
rng.AddComment Range("D28").Value

End Sub
 
Upvote 0
Oh thank god!

Joe Was' method worked.

Jay's gave the same error, 1004.

I am using VB 6.0, is that the latest?

Bizarre. I'm just glad that someone figured out one of the near infinite combinations that actually worked!


Thanks for all the help, and if you're MS, you need to tell the VB people about orthogonality.
 
Upvote 0
It's the .ClearComments that does it.

Unfortunately, subsequently doing AddComment creates a comment with the default, tiny size. This took care of that:

Set rng = Sheet1.Range("D6")
rng.ClearComments
'Direct from coded text!
rng.AddComment "Here are the files that were created:" + vbLf + outputFilesList
rng.Comment.Shape.Left = 100
rng.Comment.Shape.Top = 100
rng.Comment.Shape.width = 400
rng.Comment.Shape.Height = 400


So what have we learned today? That comments, once set, appear to be considered const, such that you have to blow them away and recreate to "change" them.
This message was edited by AChimpNamedCornelius on 2002-08-23 12:54
 
Upvote 0
On 2002-08-23 12:52, AChimpNamedCornelius wrote:
It's the .ClearComments that does it.

Unfortunately, subsequently doing AddComment creates a comment with the default, tiny size. This took care of that:

Set rng = Sheet1.Range("D6")
rng.ClearComments
'Direct from coded text!
rng.AddComment "Here are the files that were created:" + vbLf + outputFilesList
rng.Comment.Shape.Left = 100
rng.Comment.Shape.Top = 100
rng.Comment.Shape.width = 400
rng.Comment.Shape.Height = 400
This message was edited by AChimpNamedCornelius on 2002-08-23 12:54

or try

rng.Comment.Shape.TextFrame.AutoSize = True

to fit the shape around the text.

Henk
 
Upvote 0
It's odd Jay's code and my code are the same I only added an option to load the comment text from a cell or a hard coded string?
 
Upvote 0

Forum statistics

Threads
1,218,943
Messages
6,145,354
Members
450,611
Latest member
JodiWe

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top