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


    TIA


    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:


    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.


    HTH


    Jack in the UK



    Code:


    Sub Jacks_SpecialCommentBox()




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


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


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


    ' 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!