VBA: adjusting size of comments

[JaredSorensen]

I've recorded the steps of what I want a macro to do. Unfortunately, I can't seem to size a comment correctly. I must be missing some step, or perhaps the macro doesn't "play back" correctly.

Any tips for what I need to do to get the comment to be appropriately sized to display the text is an aesthetic format? I've run into this issue before and I don't know how to solve it. Thanks.

Jared

code:

Sub Add_comment_and_adjust_size()
    Range("a1").Select
    Range("a1").AddComment
    Range("a1").Comment.Visible = False
    Range("a1").Comment.Text Text:="Jared Sorensen:" & Chr(10) & ""
    'Selection.ShapeRange.ScaleWidth 2.56, msoFalse, msoScaleFromTopLeft
    'Selection.ShapeRange.ScaleHeight 2.1, msoFalse, msoScaleFromTopLeft
    'Range("a1").Comment.Shape.Select True
    Range("a1").Comment.Text Text:= _
        "Jared Sorensen:" & Chr(10) & "Four regressions were run to quantify the relationship between Euclidean distance (as the crow flies) and PC Miler distance (actual road distance)." & Chr(10) & "" & Chr(10) & "Overall, the correlation was 99% fo"
    Range("a1").Comment.Text Text:= _
        "r y = 1.236x + 6.74." & Chr(10) & "" & Chr(10) & "For distances <15 miles, y = 1.099x + 2.02 (R2 =.83)" & Chr(10) & "For distances <25 miles, y = 1.262x + 2.10 (R2 =.80)" & Chr(10) & "For distances <50 miles, y = 1.337x + 3.05 (R2 =.91)" & Chr(10) & "" _
        , Start:=200
    'Selection.ShapeRange.ScaleWidth 1.05, msoFalse, msoScaleFromTopLeft
    'Selection.ShapeRange.ScaleWidth 1.03, msoFalse, msoScaleFromTopLeft
End Sub

 

[RoryA]

I'm not sure exactly what you are after. You can set:

Comment.Shape.Textframe.Autosize = True

if that helps?

 

[JaredSorensen]

Rorya,

Thanks, that did the trick. The only limitation that approach has is that it autosizes the width as per the longest sentence. Since my first sentence was verbose, it resulted in too wide of a comment.

To get around that, I inserted additional carriage returns Chr(10) and now it works fine. Thanks for your help.

Jared

Sub Macro9()
    Range("c23").Select
    Range("c23").AddComment
    Range("c23").Comment.Visible = False
    Range("c23").Comment.Text Text:="Jared Sorensen:" & Chr(10) & ""
    'Selection.ShapeRange.ScaleWidth 2.56, msoFalse, msoScaleFromTopLeft
    'Selection.ShapeRange.ScaleHeight 2.1, msoFalse, msoScaleFromTopLeft
    'Range("R7").Comment.Shape.Select True
    Range("c23").Comment.Text Text:= _
        "Jared Sorensen:" & Chr(10) & "Four regressions were run to quantify the relationship " & Chr(10) & "between Euclidean distance (as the crow flies) and" & Chr(10) & "PC Miler distance (actual road distance)." & Chr(10) & "" & Chr(10) & "Overall, the correlation was 99% fo"
    Range("c23").Comment.Text Text:= _
        "r y = 1.236x + 6.74." & Chr(10) & "" & Chr(10) & "For distances <15 miles, y = 1.099x + 2.02 (R2 =.83)" & Chr(10) & "For distances <25 miles, y = 1.262x + 2.10 (R2 =.80)" & Chr(10) & "For distances <50 miles, y = 1.337x + 3.05 (R2 =.91)" & Chr(10) & "" _
        , Start:=200
    'Selection.ShapeRange.ScaleWidth 1.05, msoFalse, msoScaleFromTopLeft
    'Selection.ShapeRange.ScaleWidth 1.03, msoFalse, msoScaleFromTopLeft
    Range("c23").Comment.Shape.TextFrame.AutoSize = True
End Sub