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.

Conclusion

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