Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
823 Views
