Resizing Multiple Comments

holliday50

New Member
Joined
Jul 20, 2006
Messages
16
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?
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try this:

Code:
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.
 

rashiki

New Member
Joined
Mar 12, 2014
Messages
1
Try this:

Code:
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.

You're a genius! Thanks!
 

edelmefry5

New Member
Joined
Mar 19, 2014
Messages
1
I am quite experienced in Excel, but not real macro savvy. Instructions were clear, precise and worked brilliantly! Can't thank you enough, never found an answer before! Was so impressed I actually registered on this website just to Thank you both. Also good to know it is a bug and not anything I am in advertently doing wrong.
 

lauravstuart

New Member
Joined
Jul 6, 2015
Messages
1

ADVERTISEMENT

Thank you so much for putting up such simple and concise instructions on a workaround!!! I also registered on this site just so that I could thank you!
 

lordway

New Member
Joined
Jul 29, 2015
Messages
1

ADVERTISEMENT

This works great, however, I have thumbnail pictures in my comments and when I ran this macro it resized the comment boxes smaller to only fit the user name who added the comment and not the picture. I would like a similar macro that allowes me to set the thumbnail picture size to something larger that the comment box default.

Thanks in advance to anyone who can help with a similar/modified macro!



Try this:

Code:
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.
 

adunbar

New Member
Joined
Sep 1, 2015
Messages
1
Let me echo what others have said. I registered just so I could say thank you. I have struggled with this issue for years, and you made it so easy to solve. THANK YOU!
 

hoohoolian

New Member
Joined
Sep 21, 2015
Messages
8
OK, VoG, that works as stated. However, this does not wrap text. My comments are very long, sometimes 100 words, and the comments are now unreadable, reaching absurdly far off the page. More importantly, my issue with this bug is in editing comments. Example: I am at row 10, column AD; hovering, the comment appears next to that box. When I click edit comment, the box opens at row 1447, column A (this is consistent for any comment in the same column, but each column gets sent to different areas, e.g ). I cannot move the edit-comment-box further than the visual page. I must either copy the content, delete comment and re-insert comment -- OR -- reduce the view to 1% and slide, slide, slide -- then open view back to 100%.



Try this:

Code:
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.
 

john jenkins

New Member
Joined
Oct 1, 2015
Messages
1
I've the same issue. The only work around I've found is tedious. It involves placing a period (.) in the lower right corner of the comment to fool Excel into resizing the comment based on text that is only spaces and the final period. First size the comment to fit the image. Then edit the comment using the return key to move the cursor down to the bottom of the comment then space all the way over to the right side and enter a period. The period will show up on your image, but you probably won't notice it unless you're looking. When you run the macro, Excel will resize based on the text which now fits your image.

This works great, however, I have thumbnail pictures in my comments and when I ran this macro it resized the comment boxes smaller to only fit the user name who added the comment and not the picture. I would like a similar macro that allowes me to set the thumbnail picture size to something larger that the comment box default.

Thanks in advance to anyone who can help with a similar/modified macro!
 

Watch MrExcel Video

Forum statistics

Threads
1,132,670
Messages
5,654,653
Members
418,146
Latest member
Shnn028

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top