VBA Macros

Pimp your comment boxes [because it is Friday]

Last updated on September 10, 2009

Chandoo

Excel comment boxes are a very useful feature, but the comment box look hasn’t changed since slice bread. So Tom, one of our readers, took it upon himself to revamp the comment box. He wrote a simple macro to botox, smoothen and color the comment box. It is a fun and simple macro, something that can make a boring spreadsheet friday a little more exciting.

Here is the code:

Sub Comments_Tom()

Dim MyComments As Comment

Dim LArea As Long

For Each MyComments In ActiveSheet.Comments

With MyComments

.Shape.AutoShapeType = msoShapeRoundedRectangle

.Shape.TextFrame.Characters.Font.Name = "Tahoma"

.Shape.TextFrame.Characters.Font.Size = 8

.Shape.TextFrame.Characters.Font.ColorIndex = 2

.Shape.Line.ForeColor.RGB = RGB(0, 0, 0)

.Shape.Line.BackColor.RGB = RGB(255, 255, 255)

.Shape.Fill.Visible = msoTrue

.Shape.Fill.ForeColor.RGB = RGB(58, 82, 184)

.Shape.Fill.OneColorGradient msoGradientDiagonalUp, 1, 0.23

End With

Next  'comment

End Sub

Give it a try, I am sure you can afford some lipstick and a new pair of shoes for the comment boxes.

Related material on comment boxes: