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.
But the actual comment is below and excel had resized it even though I had saved it by making the comment box larger.
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.
Step 2
You will see a screen like this and now insert a module from the menu tab as shown below.
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
Next
End Sub
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.
Step 5
As you could see below for Comment 1, the vba code has resized the comment boxes in excel automatically.
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.
