• Excel Video Tutorials

How to Auto-Size a Comment Box to Fit Its Content in Excel?

Have you ever wondered if there is any way in Excel to automatically size the comment box to fit its contents? This is only possible using the VBA application. Using VBA code, we can customise the sheet to automatically fit the size of the comment box to its contents in Excel. This tutorial will help you understand how we can automatically size comment boxes to fit their content in Excel.

Auto-Size a Comment Box to Fit Its Content in Excel

Here, we will first create a VBA module and then run it to complete our task. Let us see a simple process to understand how we can auto-size the comment box to fit its contents using the VBA application.

Step 1

Consider an Excel sheet with at least one comment present throughout the sheet, as shown in the image.

Now to open the VBA application, click on "Insert," select "Module," and enter the programme into the textbox as shown in the below image.

Example 1

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

Step 2

Now save the document as a macro-enabled sheet and click on F5 to run the code, and the comments will be adjusted as shown in the below image.

If you only want to auto-size the comments in a range of cells, then we can use Program 2 in the VBA application.

Example 2

Sub Fitrangecomments() 'Update By Nirmal Dim rng As Range Dim WorkRng As Range xTitleId = "Select the range" 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

Note − The above codes will only work for the existing comment boxes; they will not work for newly added comment boxes.


In this tutorial, we used a simple example to demonstrate how you can auto-size a comment box to fit its contents in Excel.