All about Comment shapes

Chandoo posted some code one of his readers submitted, it "pimps" your comment boxes from those boring black-text-on-yellow rectangles to something more professional and eye-pleasing. See his post for the original code.

I think the code just screams for some more With blocks. Plus, you should be able to pick which worksheet you want to act on (including the active sheet). So I would write it like this:

Sub ChangeCommentBox(sht As Excel.Worksheet)
' based on http://chandoo.org/wp/2009/09/11/format-comment-box/
Dim Comment As Excel.Comment
Dim Comments As Excel.Comments

  Set Comments = sht.Comments

  For Each Comment In Comments

    With Comment.Shape
      .AutoShapeType = msoShapeRoundedRectangle

      With .TextFrame.Characters.Font
        .Name = "Tahoma"
        .Size = 8
        .ColorIndex = 2
      End With

      With .Line
        .ForeColor.RGB = RGB(0, 0, 0)
        .BackColor.RGB = RGB(255, 255, 255)
      End With

      With .Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(58, 82, 184)
        .OneColorGradient msoGradientDiagonalUp, 1, 0.23
      End With

    End With
  Next Comment

End Sub

So now, if you wanted to change all the comment boxes on every sheet, you would call the function like this:

Sub Test()

Dim sht As Excel.Worksheet
Dim wkbk As Excel.Workbook

  Set wkbk = ActiveWorkbook

  For Each sht In wkbk.Worksheets
    Call ChangeCommentBox(sht)
  Next sht

End Sub

Or to change the active sheet (like the original procedure), just call ChangeCommentBox(ActiveSheet). Although I recommend the loop, so all the comments have a consistent look for your application.

But wait, we can't stop here. Look at all the hard coded values in the procedure! If we just leave it as is, we'll be stuck with all the parameters in the original function. So let's rewrite it so we can change any of the values.

Sub ChangeCommentBox(sht As Excel.Worksheet, _
                 Optional boxType As MsoAutoShapeType = msoShapeRoundedRectangle, _
                 Optional gradientStyle As MsoGradientStyle = msoGradientDiagonalUp, _
                 Optional fontName As String = "Tahoma", Optional fontSize As Long = 8, _
                 Optional fontColor As Long = 2, Optional lineForeColor As Long = 0, _
                 Optional lineBackColor As Long = 1, Optional fillEffectsVariant As Long = 1, _
                 Optional gradientDegree As Double = 0.23, Optional fillForeColor As Long = 18)
' based on http://chandoo.org/wp/2009/09/11/format-comment-box/

Dim Comment As Excel.Comment
Dim Comments As Excel.Comments

  Set Comments = sht.Comments

  For Each Comment In Comments

    With Comment.Shape
      .AutoShapeType = boxType

      With .TextFrame.Characters.Font
        .Name = fontName
        .Size = fontSize
        .ColorIndex = fontColor
      End With

      With .Line
        .ForeColor.SchemeColor = lineForeColor
        .BackColor.SchemeColor = lineBackColor
      End With

      With .Fill
        .Visible = msoTrue
        .ForeColor.SchemeColor = fillForeColor
        .OneColorGradient gradientStyle, fillEffectsVariant, gradientDegree
      End With

    End With
  Next Comment

End Sub

There are 11 possible parameters for this function! But now we can set default values for each, and ignore whichever ones we don't want to change. So I can still call this function using the Test() procedure and get the same result. But if I wanted a different shape (a 5-point star, for example) I could call it like this:

Sub Test()

Dim sht As Excel.Worksheet
Dim wkbk As Excel.Workbook

  Set wkbk = ActiveWorkbook

  For Each sht In wkbk.Worksheets
    Call ChangeCommentBox(sht, msoShape5pointStar)
  Next sht

End Sub

The best part is the Intellisense on the shape type and gradient style. For the color index, see Excel Color Palette. One thing I noticed was Comment.Shape.Fill.ForeColor.SchemeColor doesn't follow the same pattern as Comment.Shape.Line.ForeColor.SchemeColor. So have fun exploring the different color patterns!