Resizing Multiple Comments

[holliday50]

I have a spreadsheet with over 1000 rows, with comments in 1 cell in each row. The problem is that the comment box is too small, and cuts off part of the verbiage. How can I resize all of them at once, so that everything in the comment field is visible?

 

[Sheowahya]

Try this:

Sub AutosizeComments()
    Dim cmt As Comment, cell As Range
    On Error Resume Next
    For Each cell In ActiveSheet.UsedRange
        Set cmt = cell.Comment
        If Not cmt Is Nothing Then cmt.Shape.TextFrame.AutoSize = True
    Next cell
End Sub

ALT+F11 to open the Visual Basic Editor, Insert > Module then copy and paste in the above code. Close the VBE, ALT + F8, click on AutosizeComments then click the Run button.

I will echo what others have said, AWESOME fix!!! Can't thank you enough for this!!!

but may I ask for even more?

I have a spreadsheet with HUNDREDS of comments (client files are the worst!!) and they show up around line 65,467 even though the last line used in the spreadsheet is 302.

Is there any way to get ALL of the comments to move up NEXT to their associated cells?

this is SO annoying!!!!

Eternal blessings if anyone can help me.

 

[miranor]

Try this:

Sub AutosizeComments()
    Dim cmt As Comment, cell As Range
    On Error Resume Next
    For Each cell In ActiveSheet.UsedRange
        Set cmt = cell.Comment
        If Not cmt Is Nothing Then cmt.Shape.TextFrame.AutoSize = True
    Next cell
End Sub

ALT+F11 to open the Visual Basic Editor, Insert > Module then copy and paste in the above code. Close the VBE, ALT + F8, click on AutosizeComments then click the Run button.

Indeed, a very helpful macro, though it's only a partial solution - it doesn't move the actual comments back in place, and doesn't wrap the text, making long comments unreadable.
Here's my improved solution based on the above:

------------------------
Sub AutosizeComments()
Dim cmt As Comment, cell As Range
On Error Resume Next
For Each cell In ActiveSheet.UsedRange
Set cmt = cell.Comment
If Not cmt Is Nothing Then
cmt.Shape.Placement = xlMove
cmt.Shape.Left = cell.Left + cell.Width + 30
cmt.Shape.Top = cell.Top + 10
cmt.Shape.Width = 200
cmt.Shape.Height = 150
End If
Next cell
End Sub
--------------------

This version places each comment near the top-right corner of its cell and sets its size to 200x150 points, which is small enough to fit any screen and large enough for about a 70-word comment. If you have larger comments, simply replace the "200" and "150" numbers in the code with larger numbers as you see fit.

 

[JohnS]

Try this:

Sub AutosizeComments()
    Dim cmt As Comment, cell As Range
    On Error Resume Next
    For Each cell In ActiveSheet.UsedRange
        Set cmt = cell.Comment
        If Not cmt Is Nothing Then cmt.Shape.TextFrame.AutoSize = True
    Next cell
End Sub

ALT+F11 to open the Visual Basic Editor, Insert > Module then copy and paste in the above code. Close the VBE, ALT + F8, click on AutosizeComments then click the Run button.

The Power of the SpecialCells Method
Regarding Holliday50's post, and the related comments:
Wouldn't it speed things up if the VBA procedure doesn't have to look at every cell in a selected range, or, worse, in the entire Used Range?

I have had good results by applying the SpecialCells method. A side advantage is that the proc doesn't require any object variables to be declared.

The following procedure is working for me in Excel 2003 and Excel 2013. It checks all the comments in the sheet's Used Range.

Sub Comments_Resize_Selected()
' Created 1/25/2016, JohnS
' (Look:  no object variables declared)

   For Each c In ActiveSheet.UsedRange.Cells.SpecialCells(xlCellTypeComments)
      c.Comment.Shape.TextFrame.AutoSize = True
   Next c
   
   MsgBox "Procedure completed."
End Sub

Example: if this procedure is run in a worksheet with a 100 x 60-sized Used Range, and two of those cells contain comments, then instead of examining 6,000 cells it only has to examine 2 cells.

Further:
Want to move the comments that have moved away from the parent cells back to where they belong?
Adding these statements inside the For...Next statement makes each comment return to its parent cell (or one cell away...):
c.Comment.Shape .Left = c.Offset(0, 1).Left
c.Comment.Shape .Top = c.Offset(-1, 1).Top

 

[miranor]

The Power of the SpecialCells Method
Regarding Holliday50's post, and the related comments:
Wouldn't it speed things up if the VBA procedure doesn't have to look at every cell in a selected range, or, worse, in the entire Used Range?

I have had good results by applying the SpecialCells method.

I just tried adding 'SpecialCells' as specified. Works fine but takes exactly the same time as before, without 'SpecialCells' (about 2 seconds for 10,000 cells). Don't see any difference.

 

[JohnS]

Re: Resizing Multiple Comments -- Two very different approaches!

miranor:
Thanks for the feedback.
Now, I'm curious why that would happen...

Does anyone have any further insight why Proc A, which looks at each of 10,000 cells, sets an object variable for each one, and tests each one for the presence of a comment, is taking the same time as Proc B, which runs a SpecialCells(xlCellTypeComments) function on the full range of 10,000 cells and then does more action on just the two cells that resolve from the SpecialCells function -- and doesn't declare any object variables in the process?

This could be interesting! Thanks in advance for your ideas & assistance.
JohnS

 

[terrif]

Thank you all so much for this great help! I'm happy to have found this site

Indeed, a very helpful macro, though it's only a partial solution - it doesn't move the actual comments back in place, and doesn't wrap the text, making long comments unreadable.
Here's my improved solution based on the above:

------------------------
Sub AutosizeComments()
Dim cmt As Comment, cell As Range
On Error Resume Next
For Each cell In ActiveSheet.UsedRange
Set cmt = cell.Comment
If Not cmt Is Nothing Then
cmt.Shape.Placement = xlMove
cmt.Shape.Left = cell.Left + cell.Width + 30
cmt.Shape.Top = cell.Top + 10
cmt.Shape.Width = 200
cmt.Shape.Height = 150
End If
Next cell
End Sub
--------------------

This version places each comment near the top-right corner of its cell and sets its size to 200x150 points, which is small enough to fit any screen and large enough for about a 70-word comment. If you have larger comments, simply replace the "200" and "150" numbers in the code with larger numbers as you see fit.

 

[JohnS]

I just tried adding 'SpecialCells' as specified. Works fine but takes exactly the same time as before, without 'SpecialCells' (about 2 seconds for 10,000 cells). Don't see any difference.

miranor:

I'm back... This time, I have a proc which works on a number of properties of each comment on a worksheet...

The following proc...

• list Jumps to cells that contain comments, bypassing all cells without comments
• list Changes the type font to be non-bold, "Calibri", size 8
• list Selects the property "Move and Size Comment with Cell"
• list Applies Autosize
• list Moves any wandering comments back close to parent cell

Try it, and let me know the results.
I am using Excel 2003 SP3, with Win 7 Home Premium SP1

Sub Comments_All_PropertiesB()
' Created 2/11/2017, adapted from Comments_All_PropertiesA _
      5/28/2012, JohnS
' For all existing cell comments on a wksht, applies automatic size, _
   "Move and Size Comment with Cell", moves any wandering comments _
   back close to parent cell, and font to Calibri 8 Regular (not Bold)
' NOTE:  Proc could fail if a cell's comment is hidden (not visble)
    
   For Each c In ActiveSheet.UsedRange.Cells.SpecialCells( _
         xlCellTypeComments).Cells
'     The next 2 statements are for procedure-debugging only:
      MsgBox "There are " & CStr(ActiveSheet.UsedRange.Cells. _
         SpecialCells(xlCellTypeComments).Count) & " cells " & _
         "currently selected by the Procedure.", vbInformation _
         + vbOKOnly
      c.Select ' for use during debugging only
      
      With c.Comment.Shape
            With .TextFrame.Characters(1).Font
                .Bold = False
                .FontStyle = "Calibri"
                .Size = 8
            End With
            .TextFrame.AutoSize = True
            .Placement = xlMoveAndSize
            .Left = c.Offset(0, 1).Left
            .Top = c.Offset(-1, 1).Top
      End With
   Next c
    
   MsgBox "Procedure completed."

End Sub
[code]

 

[karluxi]

Hello to all who had problems with finding their comments that scattered around the spreadsheet. After i used the resizing macro in this thread, I found this one to bring all the comments to the parent cell. apply same procedure with VBA. That is, Alt+F11, in VBA, instert module and copy paste this text:

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


Close VBA, press alt+F8 in excel, and when macro opens with "resetComments", press Run.

They should all go back to where they belong!

Hope it helps, I just registered to thanks the macro on resizing, and give back some useful tip (which I found here: http://blog.contextures.com/archives/2011/02/16/fix-those-wandering-excel-comments/)

 

[jinref77]

Try this:

Sub AutosizeComments()
    Dim cmt As Comment, cell As Range
    On Error Resume Next
    For Each cell In ActiveSheet.UsedRange
        Set cmt = cell.Comment
        If Not cmt Is Nothing Then cmt.Shape.TextFrame.AutoSize = True
    Next cell
End Sub

ALT+F11 to open the Visual Basic Editor, Insert > Module then copy and paste in the above code. Close the VBE, ALT + F8, click on AutosizeComments then click the Run button.

My first time leaving a comment.... I been looking for this solution since long time ago and nothing .... I've tried couple of recommendations but nothing until today.... thank you for putting up clear and precise instructions.... this works great... and you made it so easy...
Thank You... !!!!

 

[marble5]

Try this:

Sub AutosizeComments()
    Dim cmt As Comment, cell As Range
    On Error Resume Next
    For Each cell In ActiveSheet.UsedRange
        Set cmt = cell.Comment
        If Not cmt Is Nothing Then cmt.Shape.TextFrame.AutoSize = True
    Next cell
End Sub

ALT+F11 to open the Visual Basic Editor, Insert > Module then copy and paste in the above code. Close the VBE, ALT + F8, click on AutosizeComments then click the Run button.

Same as others - I have also registered just so I could post here and say THANKS! It worked for me as well, this is great!