Automatically Resize Comment Boxes In Excel

In this post we will see how we could resize multiple comment boxes to fit the contents in excel at once.

We all at some point may have faced this problem of every time opening the excel file and seeing almost all the comments keeps getting resized and we can’t read the comments anymore.

Each time we would have to resize each comment again just to read the contents of comment box even after saving then opening the file again, it still gets resized and just thinks if there are a lot of comments, it would waste a lot of time going to each and every comment boxes and resize them manually.

Below is the pic showing not clearly legible comments as excel has resized the comment box after we have opened the file.

Comment Box not showing content properly

But the actual comment is below and excel had resized it even though I had saved it by making the comment box larger.

Comment Box showing content properly

We will use small VBA code to resize each comment box in excel to fit their content and will avoid manually resizing the comments in excel.

Step 1

Open the VB editor under Developer tab in excel as shown in the pic below.

open VB editor to paste the code to resize comment box

Step 2

You will see a screen like this and now insert a module from the menu tab as shown below.

insert module to paste the code to resize comment box

Step 3

Now paste the code below in the module which will automatically resize multiple comment boxes to fit their contents in excel.

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

pastinf vba code in the module to resize comment box

Step 4

To run the VBA code to resize comment boxes in excel, hit the play button as hown in the pic below or press F5.

running vba code to automatically resise comment boxes in excel

Step 5

As you could see below for Comment 1, the vba code has resized the comment boxes in excel automatically.

comment box fit to content

Irrespective of the number of comments that you have in an excel file, you need not worry about manually resizing each of the comment boxes to fit to their contents.

This VBA code will resize them for you.

Hope this helped.




Share The Knowledge

Random Posts

Leave a Reply