Microsoft Office Tutorials and References
In Depth Information
The Comment Object: A Case Study
I’ll be the first to admit it: This process can get very confusing! But, as an example of the
elegance of VBA, you can write a single statement to change the color of a comment:
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 ), the code is
Worksheets(“Sheet1”).Comments(1).Shape.Fill.ForeColor _
.SchemeColor = 12
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 the ColorIndex property to 5 :
Worksheets(“Sheet1”).Comments(1) _
.Shape.TextFrame.Characters.Font.ColorIndex = 5
Refer to Chapter 30 for more information on colors.
Determining whether a cell has a comment
The following statement displays the comment in cell A1 of the active sheet:
MsgBox Range(“A1”).Comment.Text
If cell A1 doesn’t have a comment, executing this statement generates a cryptic error message:
Object variable or With block variable not set .
To determine whether a particular cell has a comment, you can write code to check whether the
Comment object is Nothing . (Yes, Nothing is a valid keyword.) The following statement
displays True if cell A1 doesn’t have a comment:
MsgBox Range(“A1”).Comment Is Nothing
 
Search JabSto ::




Custom Search