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é

 

[Dave Patton]

my comment has the following text
"This comment is Arial 12; see Sub FormatComments. It also removes the default name and it will autosize the comment."

The sub follows
Sub FormatComments()
Dim oComment As Comment, i As Integer
For Each oComment In ActiveSheet.Comments
With oComment.Shape.TextFrame.Characters
i = InStr(1, .Text, ":" & vbLf)
If i > 0 Then
.Text = Mid(.Text, i + 2)
End If

With .Font
.Name = "Arial"
.Size = 12
.Bold = True
End With
End With
oComment.Shape.TextFrame.AutoSize = True
Next

End Sub


2. red indicator

I did not test the following line

You could possibly use:
Application.DisplayCommentIndicator = xlNoIndicator


Please advise if the above helps.

 

[Herve]

Dave,

Thanks a lot for your input. The formatting works beautifully.

Unfortunally, "falsing" the DisplayCommentIndicator also turns off the auto-opening of the comment when the cursor goes over the cell. You then have to right-click to display the comment from the menu.

I have asked for a perfect understanding of the Objects thinggy for Father Day. Will see...

Thanks again and have a great week-end.

 

[Alriemer]

Trying to use this code to increase font size in comments - one question:

This file is routinely shared among several users, and our comments are automatically tagged with our system user names. This code seems to scrub those names out. Any suggestions for retaining them?

Thanks
alriemer

 

[Dave Patton]

Just delete the lines that do not relate to formatting.

It is your choice to leave the name information ( Excel's default).

 

[Alriemer]

Cool, got it. Thanks much. Used on a sheet that is formatted with 16 pt font but viewed at 50% to reduce area occupied by borders and grouping.
alriemer

 

[Herve]

Dave,

I now want to change the format of a single cell, not the whole page as we do with the code above (I just want to reformat one cell in order to speed up processing time).

Is there a way to do that?

Thanks!

 

[Dave Patton]

Did you try to change the format?

While in Comment, right-clcik and access the format comment dialogue box.

 

[Herve]

Dave,

I want to do it automatically from VBA

 

[Dave Patton]

Experiment with something like


Sub FormatComments2()

Range("F2").Comment.Shape.TextFrame.Characters.Font.Size = 12


End Sub