Change default font and font size on COMMENTS in cells

**Thistledown** · 11-23-2012, 08:30 AM

How do I do this? At the moment when I insert a comment, the default font size is too small.

**JosephP** · 11-23-2012, 08:34 AM

the only way to change the defaults is to change the windows tooltip settings. in my opinion it's easier to use a macro to insert comments or reformat them

**Thistledown** · 11-23-2012, 08:37 AM

Thanks. What is involved in changing the windows tooltip settings? And how would a macro work if the comments are all bespoke and for different cells?

**JosephP** · 11-23-2012, 09:09 AM

in current versions of windows I'm not entirely sure! in previous versions you could customize the appearance through the display option in control panel
you can use

Sub addMyComment()
   Dim rCell                  As Range
   For Each rCell In Selection
      With rCell
         .AddComment "JP: " & vbLf & InputBox(prompt:="Enter comment text", Title:="New comment")
         With .Comment.Shape
            With .TextFrame
               With .Characters.Font
                  .Name = "Calibri"
                  .Size = 18
               End With
               .HorizontalAlignment = xlCenter
               .VerticalAlignment = xlCenter
               .ReadingOrder = xlContext
               .AutoSize = True
            End With   ' .TextFrame
            .AutoShapeType = msoShapeRoundedRectangle
         End With   ' .Comment
      End With   ' rCell
   Next rCell
End Sub

to insert a formatted comment in each cell in the selection