Everyone, who has worked with Excel more than a year knows that the comments are actually quite useful – they allow us to put info data into a cell, without actually putting data in the cell.
However, the standard way of having the Excel comments is somehow unpleasant – the yellow color is a bit too much and usually we want something else.
At this point, #VBA is coming with plenty of ways to edit the outlook of the comment shape. Just to name a few of the properties here:
|
|
.AutoShapeType = msoShapeRoundedRectangle .ScaleHeight 1.5, msoFalse, msoScaleFromTopLeft .ScaleWidth 2, msoFalse, msoScaleFromTopLeft .TextFrame.Characters.Font.Name = "Tahoma" .TextFrame.Characters.Font.Size = 12 .TextFrame.Characters.Font.ColorIndex = 1 .Line.ForeColor.RGB = RGB(0, 0, 0) .Line.BackColor.RGB = RGB(255, 255, 255) .Fill.Visible = msoTrue .Fill.ForeColor.RGB = RGB(255, 204, 153) .Fill.OneColorGradient msoGradientDiagonalUp, 1, 0.25 .Line.DashStyle = msoLineLongDash .Shadow.Visible = msoFalse .Placement = xlMoveAndSize |
Actually, with this part of the code, you may make a beautiful comment, like the one in the picture. And anything is editable, if you press Ctrl + Space, while editing. E.g., if you change the line, concerning the
AutoShapeType to this –
.AutoShapeType = msoShapeActionButtonMovie, then your comment will be looking like this one:
If you want to have the comments shown as a default, then set the
commentVisible parameter to
True. Enjoy it:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
Sub BeautifyComments(myCell As Range, commentText As String, Optional commentVisible As Boolean = False) myCell.ClearComments myCell.AddComment.Visible = commentVisible myCell.Comment.Text commentText With myCell.Comment.Shape .AutoShapeType = msoShapeActionButtonMovie .ScaleHeight 1.5, msoFalse, msoScaleFromTopLeft .ScaleWidth 2, msoFalse, msoScaleFromTopLeft .TextFrame.Characters.Font.Name = "Tahoma" .TextFrame.Characters.Font.Size = 12 .TextFrame.Characters.Font.ColorIndex = 1 .Line.ForeColor.RGB = RGB(0, 0, 0) .Line.BackColor.RGB = RGB(255, 255, 255) .Fill.Visible = msoTrue .Fill.ForeColor.RGB = RGB(255, 204, 153) .Fill.OneColorGradient msoGradientDiagonalUp, 1, 0.25 .Line.DashStyle = msoLineLongDash .Shadow.Visible = msoFalse .Placement = xlMoveAndSize End With End Sub |
That’s all, folks!
VBA – Make Excel Comments Beautiful
Everyone, who has worked with Excel more than a year knows that the comments are actually quite useful – they allow us to put info data into a cell, without actually putting data in the cell.
However, the standard way of having the Excel comments is somehow unpleasant – the yellow color is a bit too much and usually we want something else.
At this point, #VBA is coming with plenty of ways to edit the outlook of the comment shape. Just to name a few of the properties here:
Actually, with this part of the code, you may make a beautiful comment, like the one in the picture. And anything is editable, if you press Ctrl + Space, while editing. E.g., if you change the line, concerning the AutoShapeType to this – .AutoShapeType = msoShapeActionButtonMovie, then your comment will be looking like this one:
If you want to have the comments shown as a default, then set the commentVisible parameter to True. Enjoy it:
That’s all, folks!
Related posts: