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:

        .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:

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!