How to Automatically Size Comment Box to Fit Its Content in Excel

This post will guide you how to automatically size comment box to fit its content in a selected range of cells in Excel. How do I auto-size comment box to fit its content with VBA Macro code in Excel.

Auto-size Comment Box to Fit its Content


If you want to auto-size comment box to fit its content in a range of cells in worksheet, you can use an Excel VBA Macro to achieve the result quickly. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

convert column number to letter3

#4 paste the below VBA code  into the code window. Then clicking “Save” button.

size comment box fit content1

Sub AutoFitCommentBox()
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select one Range that contain comment boxes:", "AutoFitCommentBox", myRange.Address, Type:=8)
    For Each oneCell In myRange
        If Not oneCell.Comment Is Nothing Then
            oneCell.Comment.Shape.TextFrame.AutoSize = True
        End If
    Next
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

size comment box fit content2

#6 Select one Range that contain comment boxes. Click Ok button.

size comment box fit content3size comment box fit content3

#7 let’s see the result:

size comment box fit content4

 

Comments

So empty here ... leave a comment!

Leave a Reply

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

Sidebar