All about Comment shapes
October 2, 2009 @ 8:00 AM by JP • 1 views • No Comments »
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:
' 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:
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.
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:
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!
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 if you want to learn more about VBA. Keep Reading »
Previous Post: Working with the del.icio.us API in VBA
Next Post: Q3 Stats Update
I usually use a custom color palette, so I recommend changing:
'.colorindex = 2'
to
'.color = rgb(255,255,255)'
Hi JP,
Thanks for the further exploration and development of the code. Just what I needed.
Cheerio
Tom
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
)
this is a really well written script.