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.
#2 then the “Visual Basic Editor” window will appear.
#3 click “Insert” ->”Module” to create a new module.
#4 paste the below VBA code into the code window. Then clicking “Save” button.
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.
#6 Select one Range that contain comment boxes. Click Ok button.
#7 let’s see the result: