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!

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »



Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 3 Comment(s) on All about Comment shapes:

  1. I usually use a custom color palette, so I recommend changing:

    '.colorindex = 2'
    to
    '.color = rgb(255,255,255)'

  2. Hi JP,

    Thanks for the further exploration and development of the code. Just what I needed.

    Cheerio
    Tom

  3. Very good addition to post Jimmy. (I just saw it, for some reason, there wasnt a pingback and I am on a vacation, so no google reader either :D)

    this is a really well written script.

This article is closed to any future comments.
Peltier Tech Charting Utilities for Excel