VBA Function to Autosize Comment Box with Fixed Width

I searched and searched and searched for VBA code that would allow me to autosize comment boxes, but retain a fixed width.  This is very challenging and I will explain a little, but skip the short story and see the full code at the end of the post.

The reason I needed this code was to automate a process of inputting data from one workbook into comment boxes across several worksheets in another workbook.  The job is done manually, which is a huge time sink when you need to be spending more time analyzing the data.  What I am sharing is only a piece of the code used to complete this task.  I plan to use this code on subsequent projects, which is why I felt it was worth sharing with the rest of the online world and help others automate these senseless tasks.

There is a built in function within VBA to autosize a comment box and it is easily declared like this:  ActiveCell.Comment.Shape.TextFrame.AutoSize = True

The problem with the autosize VBA function is that it sizes the comment box with no regard to the current visible space of the spread sheet.  In most cases it will make the comment excessively wide to accommodate the full width of each text line.  No human would intentionally set the comment box that wide.  You would think Microsoft could program the Autosize function to use some level of max width and set height to allow all word wrapped text to still be visible.  Microsoft developers are getting better at improving Excel, but this seems to be such an easy thing fix.  Low hanging fruit if you ask me.  Although finding a solution using existing VBA functionality is not an easy task.

There are solutions that are over simplified such as here, here and here.  The simplified solutions did not work well and many times resulted in excess height, especially with a large quantity of text.  I wanted the height to be just right to allow all text to be visible within the comment box.  I came across this example here.  It worked great for my purposes.  It did lack precision due to the use of the Len function (counts number of characters in a string, including spaces).

The reason Len function is not best is because the width of the comment box is stated in Points (similar to Pixels, but not the same; Conversion: 96 Pixels per 72 Points) and not in the quantity of characters.  The calculation tries to adjust for this short coming.  My guess is the person that posted the original code discovered the required adjustment through trial and error.  I tried to find a solution that would find the width of text in Points.  I was not successful.  If anyone knows the answer, then please share in the comments.

I modified the original code to suit my needs, clean up unnecessary parts, and add notes to help understand each step.  Here is the code:

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Popular Posts

To Top