Microsoft Office Tutorials and References
In Depth Information
The Comment Object: A Case Study
Note that I use the Is keyword and not an equal sign.
You can take this one step further and write a statement that displays the cell comment only if
the cell actually has a comment (and does not generate an error if the cell lacks a comment). The
statement that follows accomplishes this task:
If Not Range(“A1”).Comment Is Nothing Then _
MsgBox Range(“A1”).Comment.Text
Notice that I used the Not keyword, which negates the True value that’s returned if the cell has
no comment. The statement, in essence, uses a double-negative to test a condition: If the
comment isn’t nothing, then display it. If this statement is confusing, think about it for a while, and it
will make sense.
Adding a new Comment object
You may have noticed that the list of methods for the Comment object doesn’t include a method
to add a new comment. This is because the AddComment method belongs to the Range object.
The following statement adds a comment (an empty comment) to cell A1 on the active worksheet:
If you consult the Help system, you discover that the AddComment method takes an argument
that represents the text for the comment. Therefore, you can add a comment and then add text
to the comment with a single statement:
Range(“A1”).AddComment “Formula developed by JW.”
The AddComment method generates an error if the cell already contains a comment. To
avoid the error, your code can check whether the cell has a comment before adding
If you’d like to see these Comment object properties and methods in action, check out
the example workbook on the companion CD-ROM. This workbook, named comment
object.xlsm , contains several examples that manipulate Comment objects with VBA
code. You probably won’t understand all the code at this point, but you will get a feel
for how you can use VBA to manipulate an object.
Search JabSto ::

Custom Search