The Comments collection

Recall that a collection is a group of like objects. Every worksheet has a Comments collection, which consists of all Comment objects on the worksheet. If the worksheet has no comments, this collection is empty. Comments appear in the collection based on their position in the worksheet: Left-to-right and then top-to-bottom.

For example, the following code refers to the first comment on Sheetl of the active workbook:


The following statement displays the text contained in the first comment on Sheetl: MsgBox Worksheets("Sheetl").Comments(l).Text

Unlike most objects, a Comment object does not have a Name property. Therefore, to refer to a specific comment, you must either use an index number or (more frequently) use the Comment property of a Range object to return a specific comment.

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(l).Parent.Address

Here, Comments(l) 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

0 0

Post a comment