Microsoft Office Tutorials and References
In Depth Information
The Comment Object: A Case Study
The Comments collection is also an object and has its own set of properties and methods. For
example, the Comments collection has a Count property that stores the number of items in the
collection — which is the number of Comment objects in the active worksheet. The following
statement displays the total number of comments on the active worksheet:
MsgBox ActiveSheet.Comments.Count
The next example shows the address of the cell that has the first comment:
MsgBox ActiveSheet.Comments(1).Parent.Address
Here, Comments(1) returns the first Comment object in the Comments collection. The Parent
property of the Comment object returns its container, which is a Range object. The message box
displays the Address property of the Range. The net effect is that the statement displays the
address of the cell that contains the first comment.
You can also loop through all the comments on a sheet by using the For Each-Next construct.
(Looping is explained in Chapter 8.) Here’s an example that displays a separate message box for
each comment on the active worksheet:
For Each cmt in ActiveSheet.Comments
MsgBox cmt.Text
Next cmt
If you’d rather not deal with a series of message boxes, use this procedure to print the comments
to the Immediate window in the VBE:
For Each cmt in ActiveSheet.Comments
Debug.Print cmt.Text
Next cmt
About the Comment property
In this section, I’ve been discussing the Comment object. If you dig through the Help system,
you’ll find that a Range object has a property named Comment . If the cell contains a comment,
the Comment property returns a Comment object. For example, the following code refers to the
Comment object in cell A1:
Range(“A1”).Comment
 
Search JabSto ::




Custom Search