Pimp your comment boxes [because it is Friday]

Posted on September 11th, 2009 in VBA Macros - 7 comments

Better Comment Boxes using VBA 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:

Special thanks to Tom for sharing the macro with me. Say thanks to him if you loved it as well.

| More

Excel School - Online Excel Training Program

Posts & Navigation

Tags: cell comments, formatting, guest posts, Learn Excel, macros, spreadsheets, VBA

Comments

Jeff Weir September 11, 2009

This borders on Excel soft-cell…er, soft-core…porn. My favorite kind.

Tim September 11, 2009

Wow, that is pimp-TASTIC! I have a question, as a VBA n00b: additional comment boxes stay plain unless I “run” the macro. Is there a way to change all comments, going-forward?

laguerriere September 11, 2009

hi Chandoo, well, I like the macro approach. For those who don’t like it, there is another way: just add the “draw” toolbar to the shapes toolbar (via Custom etc), click on “edit comment”, click on the auto-shape and then choose “draw” drop-down, –> modify auto-shape –> then you even can have a heart or a banner (I like the horizontal banner in in purple ) . in excel 2007, you have to add this custom menu that you choose via Excel Options –> Custom –> it is called “change/ modify auto-shape”!!!
best,

Tom September 11, 2009

@Chandoo. Great Post
@Tim : the way the macro is coded, it must be run very time.
@Community: If someone has an idea to perform it when opening an existing excel, it should be nice.
@Community: if someone has some code to revamp the commentboxes on all sheets, please share it.
@Microsoft Excel-progammers: some pimpoptions for the commentboxes should be great.

Cheerio
Tom

Yukikomi September 13, 2009

For the auto run, please add the codes in workbook:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Call Comments_Tom

End Sub

Debra September 14, 2009

Wow, that was a lot of fun… Thanks Tom!

Chandoo September 14, 2009

@Jeff… Now, 5000 people know about your favorite porn…

@Tim … you can write an event to handle the new comments. I wouldnt recommend it as it is really painful. another option is to use the macro suggested by Yukikomi. It will update comments everytime you activate the sheet.

@laguerriere: very cool

RSS feed for comments on this post. TrackBack URI