Creating and Sizing Excel Cell Comments:   Return to List

' Complete example which works well.  TRICK is -- Must make the cell visible for brief period while modifying it.  Though with Screen Updating turned off -- Who's to know???

With ActiveCell
    .AddComment "My Dog Clint" & vbLf & "is a black German Shepard"
    .Comment.Visible = True
    .Comment.Shape.Select True
    .Comment.Shape.IncrementLeft 9' This is in points and a minus number would move it to the left
    .Comment.Shape.IncrementTop 6' This is in points and a minus number would move it up
    .Comment.Shape.Height = 25
    .Comment.Shape.Width = 200
    .Comment.Shape.AutoShapeType = msoShapeRoundedRectangle
    With Selection.Font
        .Name = "Times New Roman"
        .FontStyle = "Bold"
        .Size = 8
    End With
    .Comment.Visible = False
End With
End Sub
' Options Described
With ActiveCell
     .AddComment "Team = " & MyNamesArray(2, n) & vbLf & "RCC Position = " & ThePositionTitle
     ' NOTE: For a return in a comment simply use "vbLf" - leaving out the "Cr" for carriage return
     .Comment.Shape.Height = 15' Height and width is in points
     .Comment.Shape.Width = 200' A relatively small one-line comments
     .Comment.Shape.AutoShapeType = msoShapeActionButtonCustom    ' A variety of very unusual styles this one a beveled button
         = msoShapeBalloon' Shows a little conversational balloon with text inside
         = msoShapeCan' As with a flowchart symbol for disk drive
         = msoShapeCloudCallout' As when a cartoon character is thinking something
         = msoShapeDoubleBrace' Text enclosed within braces & underlined {___}
         = msoShapeDoubleBracket' Same but with brackets [__]
         = msoShapeFlowchartAlternateProcess' Square with rounded edges -- nice
         = msoShapeRectangle' Slightly shaded rectangle -- nice
         = msoShapeRoundedRectangle' Same as the FlowchartAlternateProcess -- perhaps slightly diff
     .Comment.Shape.Visible = msoTrue' Makes comment come up with floating cursor over cell
     .Comment.Shape.Select True' Once a comment is visible -- it can be selected
     ' Once a comment is visible & selected (as with above commands) it can be moved right or left, and up or down
     .Comment.Shape.IncrementLeft -200' A negative number here moves left side of comment to left 200 points
     ' A positive number would move the comment box to the right
         .Comment.Shape.IncrementTop 100' A positive number here moves the top of the box down 200 points.
     ' NOTE: Also once a comment is visible & selected (commands shown above) you can format the text within by such as:
         With Selection.Font
             .Name = "Times New Roman"
             .FontStyle = "Bold"
             .Size = 9
             .Strikethrough = False
             .Superscript = False
             .Subscript = False
             .OutlineFont = False
             .Shadow = False
             .Underline = xlUnderlineStyleNone
             .ColorIndex = xlAutomatic
         End With
End With

See also:
Adding a Custom Menu
Adding VBA Code Programatically
Button Faces (to list all of them)
Deleting Custom Menu
Detecting Menus that are present
Menu & Toolbar Added/Deleted on Workbook Open & Close



Note to Webmaster