Setting size of comment under macro control?
 

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).

Setting size of comment under macro control?
 

Hi Don,

See Debra Dalgleish at:

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

---
Regards,
Norman



"Don Wiss" wrote in message
...
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).

Setting size of comment under macro control?
 

On Sat, 19 Nov 2005, Norman Jones wrote:

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