Autosize Comments Height, but specify set width.

[Jason Campbell]

Hi all,

I have the need to specify comments to Auto-size in respect of height, but have a specified/static width of 9.26cm.

The reason for this is that the comments are being used as a guide to requirements that must be met to enrol on a course. The comment box cannot exceed the width of 9.26cm, however must be able to auto-size in respect of height, so as to enable to written content to be displayed and viewed.

As a new fledgling to Excel, I'm now aware that there are numerous gremlins as regards to the Excel Comments and will need to make use of VBA to get the comments to do exactly what I want.

I've already made use of the below macro to realign/anchor comments that keep jumping all over the place, thanks to a post on MrExcel... all I need to do now is auto-size the comment box height and give it a static specific width of 9.26cm!!

Could anyone give me a few pointers?

Further, whenever the data is filtered, I have to run the macro below again...is there anyway that I can create a macros to automatically re-anchor the comments after clearing the filters?

I thought Microsoft Would of nailed this one by now...

Sub ResetComments()


Dim cmt As Comment
For Each cmt In ActiveSheet.Comments
cmt.Shape.Top = cmt.Parent.Top + 5
cmt.Shape.Left = _
cmt.Parent.Offset(0, 1).Left + 5
Next

End Sub

 

[tbrock]

Here is my personal addition to this effort. Clearly standing upon the shoulders of gracious giants. My contribution is mostly adding comments and introducing a few more tweaks to formating. Note that most of these could easily be made variable (maximum width, color of text, color of background, border, etc) by using input box(es) should your needs require that.

Sub FormatComments()
    Dim myComment As Comment, i As Integer
    Dim vArea As Long
   
    '*********************************************************
    '* This will resize *all* comments within the active sheet
    '* It is not a rapid process if you have lots of comments.
    '* I recommend calling it (only when needed) from a form
    '* button somewhere on the sheet.
    '*********************************************************
   
    For Each myComment In ActiveSheet.Comments
   
        With myComment.Shape
       
          '***********************************************
          '* Be sure all comments are properly placed
          '***********************************************
          .Top = myComment.Parent.Top + 5
          .Left = myComment.Parent.Offset(0, 1).Left + 5

          '***********************************************
          '* Comment background color   [42]
          '***********************************************
'          .Fill.ForeColor.SchemeColor = 1
           .Fill.ForeColor.RGB = RGB(255, 255, 255)     'White
           
          '***********************************************
          '* Comment border thickness and color
          '***********************************************
           .Line.Weight = 2#
           .Line.ForeColor.RGB = RGB(0, 0, 255)         'Blue
          
          '***********************************************
          '* Comment shapes
          '* .AutoShapeType = msoShapeRectangle
          '* .AutoShapeType = msoShapeFoldedCorner
          '* .AutoShapeType = msoShapeHorizontalScroll
          '* .AutoShapeType = msoShapeVerticalScroll
          '* .....etc etc etc
          '***********************************************
           .AutoShapeType = msoShapeRectangle
           
            With .TextFrame
                '***********************************************
                '* Comment margins
                '***********************************************
                .AutoMargins = False
                .MarginBottom = 5
                .MarginTop = 3
                .MarginLeft = 3
                .MarginRight = 3
                '***********************************************
                '* Comment text format
                '***********************************************
                With .Characters
                    With .Font
                        .Name = "Tahoma"
                        .Size = 10
                        .Bold = False
                        .Italic = False
                        .Color = RGB(0, 238, 0)    'Expected this to have green text, but shows as blue!
                    End With
                End With
               
                '***********************************************
                '* Comment autosize to be sure all text is displayed
                '* Use with size adjustment below to be sure
                '* comment is not too wide
                '***********************************************
                .AutoSize = True
            End With
           
            '***********************************************
            '* If any autosized comment is wider than 420
            '* We will calclulate the total area of the comment
            '* Height * Width.  Then reset the width to 420.
            '* Finally we will use maximum width to recalculate
            '* the height (total area / new width).
            '***********************************************

            If .Width > 420 Then
                vArea = .Width * .Height
                .Width = 420
                .Height = (vArea / .Width) * 1.1
            End If
       
            '***********************************************
            '* Note!
            '***********************************************
            '--> The above is NOT working properly for my comments
            '--> which have long lines of text (paragraphs which
            '--> depend upon word wrapping instead of hard returns
            '--> within a paragraph. Autosize makes the comment
            '--> as wide as the longest paragraph! This, in turn, creates
            '--> TONS of white space which then gets placed at the
            '--> end of the comment causing empty lines after empty
            '--> lines at the bottom of many comments.
            '***********************************************
           
        End With
    Next

End Sub

If anyone else arrives here and still has some issues (as I did), this from Debra Dalgleish might be helpful:

An explanation of all the frustrations Debra went thru to get to a solution:

Resize Excel Comments With Macro - Contextures Blog

Occasionally, for reasons unknown to anyone other than the centuries-old trolls who live in underground tunnels at Microsoft, Excel comments resize themselves. They get really wide, or narrow, and become almost impossible to read. To fix this problem, there is a macro on my website, written 13...

contexturesblog.com

If your needs surpass the code found above on this page, this link takes you to a page with (much) more intensive coding (I call it voodoo!) that should get the job done. On this page, look at 3 - Resize all comments in the selected area:

Excel Comments - Macros

Example macros for working with Excel comments. Insert, format or resize comments. Add numbers to comment cells, add pictures to comments comments.

www.contextures.com