How to auto-size comment box to fit its content in Excel?

When you insert comments to cells, the size of the comment box is the default. If you enter a lot of contents into it, some of the content will not be visible directly. Is it possible to fit the size of the comment box to its content automatically?

Download Sample File with Macros here: AutofitComments.xls

Auto-size comment box to fit its content in a worksheet

In Excel, you can drag the comment box to your need size manually one by one, but if there are numerous comment boxes need to be resized, this way will be tedious and time-consuming. In this case, you can apply the following VBA code.

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following macro in the Module window.

VBA code: Auto-size comment boxes in a worksheet

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

3. Then press F5 key to run this code, and all of the comment boxes in the active worksheet have been resized to fit their contents as following screenshot shown:

doc-resize-comment-box1
-1
doc-resize-comment-box2

Tip: This code only work in the current worksheet, and it cannot automatically resize the new comment boxes you add.

Auto-size comment box to fit its content in a range of cells

If you just need to auto-size a range of cell comment boxes, you can use the following VBA code.

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following macro in the Module window.

VBA code: Auto-size comment boxes in a range of cells

Sub Fitrangecomments()
Dim rng As Range
Dim WorkRng As Range
xTitleId = "QuadExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each rng In WorkRng
If Not rng.Comment Is Nothing Then
rng.Comment.Shape.TextFrame.AutoSize = True
End If
Next
End Sub

3. Then press F5 key to execute this code, and a prompt box will appear to remind you selecting a range of cells that you want to resize the comment boxes, see screenshot:

Comments

4. And then click OK, your selected cell comment boxes have been auto-fitted to the comment contents.

Tip: This code cannot automatically resize the new comment boxes you add.

Courtesy: extendoffice.com

 

Tagged:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

15 + 1 =

This site uses Akismet to reduce spam. Learn how your comment data is processed.