Microsoft Office Tutorials and References
In Depth Information
The Comment Object: A Case Study
If this comment were the first one on the sheet, you could refer to the same Comment object as
follows:
ActiveSheet.Comments(1)
To display the comment in cell A1 in a message box, use a statement like this:
MsgBox Range(“A1”).Comment.Text
If cell A1 doesn’t contain a comment, this statement generates an error.
The fact that a property can return an object is a very important concept — a difficult
one to grasp, perhaps, but critical to mastering VBA.
Objects within a Comment object
Working with properties is confusing at first because some properties actually return objects.
Suppose that you want to determine the background color of a particular comment on Sheet1 .
If you look through the list of properties for a Comment object, you won’t find anything that
relates to color. Rather, you must do these steps:
1.
Use the Comment object’s Shape property to return the Shape object that’s contained
in the comment.
2.
Use the Shape object’s Fill property to return a FillFormat object.
3.
Use the FillFormat object’s ForeColor property to return a ColorFormat object.
4.
Use the ColorFormat object’s RGB property to get the color value.
Put another way, getting at the interior color for a Comment object involves accessing other
objects contained in the Comment object. Here’s a look at the object hierarchy that’s involved:
Application (Excel)
Workbook object
Worksheet object
Comment object
Shape object
FillFormat object
ColorFormat object
 
Search JabSto ::




Custom Search