Formatting Cell Comments from VBA

[Herve]

I am filling the comments for my cells from VBA.

Is there a way to format the text of the comments, i.e., bold, different point size, etc.? (I have tried to record a macro, but that information is not recorded...)

Also, is there a way to get rid of the little red triangle (while keeping the comment poping up when the cursor is over the cell)?

Thanks for any help on this.

Hervé

 

[Herve]

Thanks Dave, this works like a charm.

Now for the pure sake of mastering the stuff ary: is there a way to go from:

Range("F2").Comment.Shape.TextFrame.Characters.Font.Size = 12
Range("F2").Comment.Shape.TextFrame.Characters.Font.Name = "Arial"
Range("F2").Comment.Shape.TextFrame.Characters.Font.Bold = True

to a

With... End With structure?

Comment is a though one, I think

 

[Dave Patton]

What happed when you tested using With and End With.

You can experiment and create the exact format that you prefer.

I learned my limited knowledge of VBA by trying to create a few solutions.
You probably know much more VBA than I do!

 

[Ekim]

Herve,

Building on Dave’s macro (see Notes at end):

Sub FormatComments2()
    With ActiveSheet.Range("F2").Comment.Shape

        .Fill.ForeColor.SchemeColor = 42
        .Line.Weight = 2#
        
        With .TextFrame
            .AutoSize = True
            With .Characters
                With .Font
                    .Size = 12
                    .Name = "Arial"
                    .Bold = True
                End With
            End With
        End With
    End With
End Sub

Notes:
1. The macro includes a colour scheme and adds a bold line around the Comment (amend or delete to suit).
2. Note the construct:
a) The colour scheme and the line weight goes with the Shape object.
b) AutoSize goes with the TextFrame object (which in turn goes with the Shape object).
c) Font goes with the Character object, which in turns goes with the TextFrame object (which in turn goes with the Shape object).

You get the idea.

HTH

Mike

 

[Herve]

Dave and Mike:

Thanks a lot for your input, this is really constructive and educational.

I have been writing VBA code for a few months now for a wine cellar management program I am writing for fun, and as I go and learn, I go back and try to make it leaner and leaner. It's a actually quite fun (and productive as long as I test the code before I taste the wine.)

In the past month, I have received help from Netherlands, Colombia, Canada, and Australia. Nothing to do with Excel, but still pretty cool.
Well, I'd better before I take too much bandwidth...

Last question, on the relevant side:
Mike: the idea is to minimize the number of dots (".") in the commands, correct? (I mean the fewer the dots, the most efficient the code, right?)

Thanks again to both of you.

 

[Ekim]

The idea is to minimize the number of dots (".") in the commands, correct? (I mean the fewer the dots, the most efficient the code, right?)

Yes. From Mr. John Walkenbach:

“The speed advantage of using object variables may not be apparent, but if you perform timed tests you will find that working with object variables is almost always faster than working with the objects themselves. One rule of thumb is to eliminate the number of "dots" processed” (emphasis added).
(http://j-walk.com/ss/excel/tips/tip08.htm ).

The macro in my previous post could be written as follows:
Sub FormatComments3()
ActiveSheet.Range("F2").Comment.Shape.Fill.ForeColor.SchemeColor = 42
ActiveSheet.Range("F2").Comment.Shape.Line.Weight = 2#
ActiveSheet.Range("F2").Comment.Shape.TextFrame.AutoSize = True
ActiveSheet.Range("F2").Comment.Shape.TextFrame.Characters.Font.Size = 12
ActiveSheet.Range("F2").Comment.Shape.TextFrame.Characters.Font.Name = "Arial"
ActiveSheet.Range("F2").Comment.Shape.TextFrame.Characters.Font.Bold = True

End Sub

Note that the following code is repeated 6 times:
ActiveSheet.Range("F2").Comment.Shape

Similarly, the following code is repeated 3 times:
ActiveSheet.Range("F2").Comment.Shape.TextFrame.Characters.Font

In my first macro (see previous post), there are 15 dotes. In the above code there are 36.

Regards,


Mike

 

[Herve]

Thanks Mike for the detail info