Autosize Comments (XL 2003)

[SammyB]

I am bringing in cell comments from a database. Most of the time they are long and often they are multi-paragraph. I am auto-sizing them:
<pre>dim c as Comment, dArea as Double
for each c in ActiveSheet.Comments
with c
.Shape.TextFrame.AutoSize = True
If .Shape.Width > 300 then
dArea = .Shape.Width * .Shape.Height
.Shape.Width = 300
.Shape.Height = (dArea / 300.0) * 1.2
end if
end with
next c
</pre>

This works great for single paragraphs, but for multi-paragraphs, there is extra space at the end. I can see that this is because the width only applies to a single paragraph, so I guess I'll have to split up the paragraphs and calculate the sum of the heights of each one. Anyone have a better idea? TIA --Sam

[HansV]

It's more or less the same problem as that with autosizing rows that contain cells that wrap text. Excel's text formatting algorithms aren't very accurate.

Is it really so bad if the comment is one line too high?

[SammyB]

I don't care about one line of blank space. It's only really bad when there is one long paragraph and a dozen short ones. Then there is as much white space as text lines.

[HansV]

I see what you mean. Unfortunately, calculating the height of text strings requires using Windows API functions, I don't think there's an Excel-only solution.

[SammyB]

Well, here's the best I can do w/o api. It still has too much blank space on most of the comments, but it's good enough for me.
<pre>Option Explicit

Sub ResizeComments()
Dim c As Comment, s As String, v As Variant
Dim dArea As Double, para() As String
Dim i As Integer, j As Integer
Dim h As Double, h1 As Double, hSum As Double
For Each c In ActiveSheet.Comments
With c
' Split the text into non-blank paragraphs
s = .Text
v = Split(s, Chr(10))
ReDim para(LBound(v) To UBound(v))
j = LBound(v)
For i = LBound(v) To UBound(v)
If Len(RTrim(v(i))) > 0 Then
para(j) = RTrim(v(i))
j = j + 1
End If
Next i
ReDim Preserve para(LBound(v) To j - 1)
' Get the height of a single line
.Text "Test"
.Shape.TextFrame.AutoSize = True
h1 = .Shape.Height
hSum = 0#
s = ""
' Add up the heights needed for each paragraph
For i = LBound(para) To UBound(para)
.Text para(i)
.Shape.TextFrame.AutoSize = True
If .Shape.Width > 300 Then
dArea = .Shape.Width * .Shape.Height
h = (dArea / 300#) * 1.2
' Round up to the nearest line height
h = Round((h1 - 0.05 + h) / h1) * h1
Else
h = h1
End If
s = s & para(i)
hSum = hSum + h
If i <> UBound(para) Then
' Put a blank line between paragraphs
s = s & Chr(10) & Chr(10)
hSum = hSum + h1
End If
Next i
' Update the comment text and size
.Text s
.Shape.TextFrame.AutoSize = True
If .Shape.Width > 300 Then .Shape.Width = 300
.Shape.Height = hSum
End With
Next c
End Sub
</pre>

[Jezza]

Hi Sammy

Does this do what you want?

<pre>Sub AutoSizeComments()
Application.ScreenUpdating = False
Dim x As Range, y As Long

For Each x In Cells.SpecialCells(xlCellTypeComments)
Select Case True
Case Len(x.NoteText) <> 0
With x.Comment

If .Shape.Width > 250 Then
y = .Shape.Width * .Shape.Height
.Shape.Width = 150
.Shape.Height = (y / 200) * 1.3
End If
.Shape.TextFrame.AutoSize = True
End With
End Select
Next x
Application.ScreenUpdating = True
End Sub</pre>

[fburg]

<P ID="edit" class=small>(Edited by fburg on 10-Nov-08 20:32. Clarified what's being repeated (each paragraph, not each comment).)</P>Sammy,

I once tried to do something like this. I can't find my code and I recall having some other ideas to refine my original attempt. Here is some of my thoughts if it's of any help.

Take the comment as you originally have it.

Break it up into paragraphs within the code (looking for line returns or blank paragraphs).

For each paragraph, set a comment box to that paragraph. I recall there was something I was doing with the properties of the comment box so the comment appeared on one line. I then got the size of the comment box. From this, I was able to approximate how high the comment box should be if I picked a particular width for the comment box (I didn't want the comment box to be very wide).

Repeat for each paragraph in the comment.

Once done with the loop, you should have an approximation as to how hig the comment box should be to accommodate the comment given a selected width. Set the properties of the comment box to these dimensions. Set the text of the comment box to the original comment.

Of course, this may not be very exact if words don't fill the comment box to the right margin.

HTH

Fred