Setting size of comment under macro control?

[Don Wiss]

I'm having a macro set a string to a cell comment. The string could be
short, or it could be as long as 240 characters. This is the code I'm using
to set the comment:

With Cells(10, ColSelection)
.ClearComments
.AddComment
.Comment.Visible = False
.Comment.Text Text:=cbClass.Column(1)
End With

Now the comment does not dynamically size itself. So sometimes part of the
text is clipped off. Ideally the comment is always sized correctly.

I tried recording the enlarging of a comment. The code I got was:

Selection.ShapeRange.ScaleHeight 0.42, msoFalse, msoScaleFromTopLeft

Like huh? Also this is selecting the cell. I prefer to avoid selection if
possible. I'm using xl2002.

Don <www.donwiss.com> (e-mail link at home page bottom).

 

[Norman Jones]

Hi Don,

See Debra Dalgleish at:

http://www.contextures.com/xlcomments03.html#Resize

 

[Don Wiss]

See Debra Dalgleish at:

http://www.contextures.com/xlcomments03.html#Resize

Thanks. This is what I ended up with:

Sub AddComment(S As String, R As Integer, C As Integer)
' puts properly sized comment in cell. sheet must be selected.
' arg: S - comment string
' R,C - row and column of cell

With Cells(R, C)
.ClearComments
.AddComment
With .Comment
.Text S
With .Shape
.TextFrame.AutoSize = True
If .Width > 300 Then
.Width = 200
.Height = ((.Width * .Height) / 200) * 1.2
End If
End With
End With
End With

End Sub