Sizing comments and text boxes in VBA

  • Does anyone know a VBA method to size a comment or worksheet text box to fit the text it contains. The best I can do is to multiply the LEN by an arbitrary factor, eg. 4.15, which works most of the time though obviously it's not ideal


    Richard Fuller

  • Yes, the TextEffect properties of the Shape object allow you to set Autosize equal to true, as in the example below. Also, because I only just discovered this when messing about, you can change the shape of the comment box as well. For an example, uncomment out the line of commented out code:

    Public Sub ResizeComment()
      Dim MyComment As Comment
      Set MyComment = Sheets("Sheet1").Range("A1").Comment
     ' MyComment.Shape.AutoShapeType = msoShapeWave
      MyComment.Shape.TextFrame.AutoSize = True
    End Sub

    My example is based on a comment on Sheet1 in cell A1. You can change this to whatever you like.

  • I hope this will assist you and works well and you can edit as you feel needed, does a little bit of most you can, I guess.

    Will resize and move.


    Jack in the UK


    Sub Jacks_SpecialCommentBox()

    ' Creates the comment.
    ActiveCell.AddComment ("")
    ' Can have user name in top left by adding name between ""
    ActiveCell.Comment.Visible = True

    With Selection
    'Chooses the CLOUD AutoShape from Drawing T/Bar.
    .ShapeRange.AutoShapeType = _

    'decides to have col(u)rs in the cloud.
    .ShapeRange.Fill.PresetGradient _
    msoGradientHorizontal, 1, _

    ' BOLD and Italics, felt like it!
    .Font.Name = "Arial"
    .Font.FontStyle = "Bold Italic"

    ' moves and resizes the cloud - wanted to!
    Selection.ShapeRange.Adjustments.Item(1) = -1.1562
    Selection.ShapeRange.ScaleWidth 1.4, msoFalse, msoScaleFromTopLeft

    End With

    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!