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 (or SchemeColor property) to get the color.

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, code to change the color of a comment can be written with a single statement:

Worksheets("Sheet1").Comments(1).Shape.Fill.ForeColor _ .RGB = RGB(0, 255, 0)

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

Worksheets("Sheet1").Comments(1).Shape.Fill.ForeColor _ .SchemeColor = 12

Confused by Colors?

When you gain experience with VBA and start working with setting colors for various objects, you will probably reach a head-scratching point and wonder what's going on. Keep this in mind: Excel uses a 56-color palette, and the specific colors are saved with each workbook. These are the colors that you see when you use the Fill Color button on the Excel Formatting toolbar (the same colors that are displayed in the Color tab of the Options dialog box). So what does this mean for a VBA programmer? The color that you specify in your VBA code might or might not be the color that actually appears.

Things get even more confusing. Depending on the object that you're manipulating, you'll need to deal with several different color-related objects and properties.

You can set the color of a Shape object by using either the RGB property or the SchemeColor property. The RGB property lets you specify a color in terms of its red, green, and blue components. This is used in conjunction with VBA's RGB function, which takes three arguments, each of which ranges from 0 to 255. The RGB function returns a value between 0 and 16,777,215. But, as I mention, Excel can only handle 56 different colors. Therefore, the actual color that results when you use the RGB function will be the closest color match in the workbook's 56-color palette. The SchemeColor property accepts values between 0 and 80. The Help system says virtually nothing about what these colors actually represent. They are, however, limited to the workbook's color palette.

When you're dealing with colors in a Range object, you need to access the Interior object, contained in the Range object. You have a choice of setting the color by using either the Color property or the Colorlndex property. Valid values for the Colorlndex property are 0 through 56 (0 represents no fill). These values correspond to the workbook's color palette. Unfortunately, the order of the colors displayed bears no relationship to the numbering system for the Colorlndex property, so you'll need to record a macro to determine the Colorlndex value for a particular color. Even then, there's no guarantee that the user hasn't changed the color palette for the workbook. If so, the Colorlndex could result in a color completely different from the one that you had in mind.

If you use the Color property, you can specify a color value by using the VBA RGB function. But, again, the actual color that you get will be the one closest to a color in the workbook's color palette.

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 Colorlndex properties. Here's an example that sets Colorlndex property to 5:

Worksheets("Sheet1").Comments(1) _

.Shape.TextFrame.Characters.Font.Colorlndex = 5

Was this article helpful?

0 0

Responses

  • gilly
    What do colors represent in vbe code?
    9 years ago

Post a comment