Results 1 to 7 of 7
  • Thread Tools
  1. Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Autosize Comments (XL 2003)

    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
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 13 Times in 13 Posts

    Re: Autosize Comments (XL 2003)

    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?

  4. Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Autosize Comments (XL 2003)

    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.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 13 Times in 13 Posts

    Re: Autosize Comments (XL 2003)

    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.

  6. Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Autosize Comments (XL 2003)

    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>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autosize Comments (XL 2003)

    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>

    Jerry

  8. Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,752
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Autosize Comments (XL 2003)

    <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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •