How to Auto-fit the size of a comment box of selected cell(s) to its content?

joeycheunghoyin

New Member
Joined
Sep 15, 2012
Messages
17
Hi everyone,

I am wondering within the Excel interface, is it possible to auto-fit the size of a comment box to its content?

I have searched the internet and found that a macro as follows could do the job:

Sub FitComments()
Dim c As Comment
For Each c In ActiveSheet.Comments
c.Shape.TextFrame.AutoSize = True
Next c
End Sub

However, this Macro applies to and resize all of the the comment boxes in the active sheet.

I would like to just auto-fit the comment box only to the selected cell, or even better, to multiple selected cells.

Is there anyway for me to accomplish this task?

Thanks in advance!
 

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.
Try

Code:
Sub FitComments()
Dim c As Comment
For Each c In Selection.Comments
    c.Shape.TextFrame.AutoSize = True
Next c
End Sub
 
Upvote 0
Thanks for your reply!

However, when I apply the Macro, there is an error message pop-up as follows:

Excel%20Macro%20Error.bmp


Run-time error '438':
Object doesn't support this property or method

May I ask is there any solution for the error?

Thanks in advance!


Try

Code:
Sub FitComments()
Dim c As Comment
For Each c In Selection.Comments
    c.Shape.TextFrame.AutoSize = True
Next c
End Sub
 
Upvote 0
This is almost certainly not the most efficient way of doing this but it works

Code:
Sub FitComments()
Dim c As Comment
For Each c In ActiveSheet.Comments
    If Not Intersect(c.Parent, Selection) Is Nothing Then c.Shape.TextFrame.AutoSize = True
Next c
End Sub
 
Upvote 0
It works perfectly!!!

Thank you very much for your help!

This is almost certainly not the most efficient way of doing this but it works

Code:
Sub FitComments()
Dim c As Comment
For Each c In ActiveSheet.Comments
    If Not Intersect(c.Parent, Selection) Is Nothing Then c.Shape.TextFrame.AutoSize = True
Next c
End Sub
 
Upvote 0
Hi,
I wrote the following code and it helped me.

Sub ResizeCommentsByLength()

Dim C As Comment

For Each C In ActiveSheet.Comments

With C
.Shape.Width = 300
x = Len(.Text) / 50
.Shape.Height = 10 * (Int(x) + 1)

'While Len(.Text) < .Shape.Height
' .Shape.Height = .Shape.Height + 10

End With
Next

End Sub

good luck


Hi everyone,

I am wondering within the Excel interface, is it possible to auto-fit the size of a comment box to its content?

I have searched the internet and found that a macro as follows could do the job:

Sub FitComments()
Dim c As Comment
For Each c In ActiveSheet.Comments
c.Shape.TextFrame.AutoSize = True
Next c
End Sub

However, this Macro applies to and resize all of the the comment boxes in the active sheet.

I would like to just auto-fit the comment box only to the selected cell, or even better, to multiple selected cells.

Is there anyway for me to accomplish this task?

Thanks in advance!
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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
Back
Top