Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Mar 2002
    Location
    Cincinnati
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Comment Distortions (w2kO2ksr1)

    I've been struggling with my comments. It seems every time I insert lines all my comments get twisted out of shape. I've read in the archives the ideas about using fake validation messages. Do these messages ever get distorted? Also, I now have a large number of comments: is there any way to tell them all to resize to fit the text in them? I am curious as to why MS has not fixed this problem since it has been around since '97 at least.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Comment Distortions (w2kO2ksr1)

    The following will Autosize the comments in the selected region
    Steve

    <pre>Sub SetAutosizeComments()
    Dim rCell As Range
    For Each rCell In Selection.SpecialCells(xlCellTypeComments)
    rCell.Comment.Shape.TextFrame.AutoSize = True
    Next
    End Sub
    </pre>


  3. #3
    Lounger
    Join Date
    Mar 2002
    Location
    Cincinnati
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comment Distortions (w2kO2ksr1)

    Dear Steve:
    Your response did not quite do the job, but it triggered me to figure out a way to do it. The code you posted creates comment boxes that stretch forever to the right. I guess MS treats "autosize" for a comment as if it were a row, a row with infinite extent to the right.
    Anyway, here is an alternative set of code that, while not scientific, does a fairly good job of resetting the size of all the comments that MS has distorted when rows are inserted:
    Sub AutoSizeComments()
    Dim rCell As Range
    Dim iNum As Integer, iMinRowChar As Integer, iRows As Integer
    Dim fWidth As Single, fHeight As Single
    Dim fUnitsPerRow As Single, fUnitsPerChar As Single
    iMinRowChar = 30
    fUnitsPerRow = 2
    fUnitsPerChar = 4
    For Each rCell In Selection.SpecialCells(xlCellTypeComments)
    rCell.Comment.Shape.LockAspectRatio = False
    iNum = rCell.Comment.Shape.TextFrame.Characters.Count
    If iNum < iMinRowChar Then 'set minimum width
    rCell.Comment.Shape.Width = fUnitsPerChar * iMinRowChar
    Else
    rCell.Comment.Shape.Width = fUnitsPerChar * (iNum ^ 0.5) * 3
    End If
    iRows = (iNum ^ 0.5) * 3
    rCell.Comment.Shape.Height = fUnitsPerRow * iRows + 2
    Next rCell
    End Sub

    This routine makes the shapes roughly in the ratio of 5:3. You can play with the variables to stretch it this way or that.
    One problem I am having: if I select just one cell, or if I select an entire sheet, I get an "object variable or with block variable not set" error. I can't see why. Any ideas?

  4. #4
    Lounger
    Join Date
    Mar 2002
    Location
    Cincinnati
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comment Distortions (w2kO2ksr1)

    With some further testing I've discovered that Selection.SpecialCells(xlCellTypeComments) does not return a valid range if it encounters a merged cell with comments. So the code above will work for any selection as long as no merged cell within the selection contains a comment. The following code will fix all comments except it skips merged cells, and does nothing if the selection is a single cell (still don't know why Selection.SpecialCells(xlCellTypeComments) gives an ivalid range in that simple case).
    Sub AutoSizeComments()
    Dim rCell As Range, rRange As Range
    Dim iNum As Integer, iMinRowChar As Integer, iRows As Integer
    Dim fWidth As Single, fHeight As Single
    Dim fUnitsPerRow As Single, fUnitsPerChar As Single
    iMinRowChar = 30
    fUnitsPerRow = 2
    fUnitsPerChar = 4
    On Error GoTo EmptySet
    Set rRange = Selection.SpecialCells(xlCellTypeComments)
    On Error GoTo ContinueLoop
    For Each rCell In rRange
    rCell.Comment.Shape.LockAspectRatio = False
    iNum = rCell.Comment.Shape.TextFrame.Characters.Count
    If iNum < iMinRowChar Then 'set minimum width
    rCell.Comment.Shape.Width = fUnitsPerChar * iMinRowChar
    Else
    rCell.Comment.Shape.Width = fUnitsPerChar * (iNum ^ 0.5) * 3
    End If
    iRows = (iNum ^ 0.5) * 3
    rCell.Comment.Shape.Height = fUnitsPerRow * iRows + 2
    ContinueLoop:
    Next rCell
    EmptySet:

    End Sub

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Comment Distortions (w2kO2ksr1)

    I am confused, when I autosize comments, they are autosized they don't extend past the end to the right.

    Is the problem that they are long text strings and excel doesn't auto word-wrap?

    You can add manual breaks with the enter key and excel will resize to the breaks.

    Steve

Posting Permissions

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