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 this:

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

I'll be the first to admit it: This can get very confusing! But, as an example of the elegance of VBA, the code to change the color of a comment can be written with a single statement:

Worksheets("Sheetl").Comments(l).Shape.Fill.ForeColor _ .RGB = RGB(0, 255, 0)

Or, if you use the SchemeColor property (which ranges from 0 to 80), the code is:

Worksheets("Sheetl").Comments(l).Shape.Fill.ForeColor _ .SchemeColor = l2

This type of referencing is certainly not intuitive at first, but it will eventually make sense. Fortunately, recording your actions in Excel almost always yields some insights regarding the hierarchy of the objects involved.

By the way, to change the color of the text in a comment, you need to access the Comment object's TextFrame object, which contains the Characters object, which contains the Font object. Then you have access to the Font object's Color or ColorIndex properties. Here's an example that sets

ColorIndex property to 5:

Worksheets("Sheetl").Comments(l) _

.Shape.TextFrame.Characters.Font.ColorIndex = 5

CROSS- Refer to Chapter 30 for more information on colors.


0 0

Post a comment