SubscribePimp your comment boxes [because it is Friday]
This article is about VBA Macros - 7 comments
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 SubGive 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:
- Use Data Validation Messages instead of Comment Boxes
- Programming the comment boxes using VBA
- Extract Comment Box Text using Formulas
- Change the shape of cell comments
Special thanks to Tom for sharing the macro with me. Say thanks to him if you loved it as well.
Have an Excel Question?
| Delicious | Stumble it |
« Want to become a Data God? Learn Excel Data Tables | Home | Member of Month and Other Updates »
Comments
RSS feed for comments on this post. TrackBack URI
Leave a comment
If you have an excel or charting question, please ask in the forums Click here to join our discussion forums
This borders on Excel soft-cell…er, soft-core…porn. My favorite kind.
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?
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,
@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
For the auto run, please add the codes in workbook:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Call Comments_Tom
End Sub
Wow, that was a lot of fun… Thanks Tom!
@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