Public Function AutoSizeCommentBox(Optional cellComment As Comment, Optional maxWidth As Integer)
Dim textArr As Variant 'Comment text deliminted to string array
Dim elements As Integer 'textArr Upper Bound
Dim textWidthArr() As Integer 'Array of length of each element of textArr array
Dim maxLine As Integer 'Max line length from textWidthArr array, then modified
Dim lineHeight As Single 'Height of each line, stated in points (not pixels)
Dim wf As WorksheetFunction 'Shorten the way WorksheetFunction is called
Set wf = WorksheetFunction 'Set wf as the key word WorksheetFunction
Dim linesNeeded As Integer 'Lines needed to increase comment box height
If maxWidth = 0 Then
'Set default to 250
maxWidth = 250
End If
'Exit sub if no cell is active
If cellComment Is Nothing Then
If ActiveCell Is Nothing Then
Exit Function
Else
Set cellComment = ActiveCell.Comment
'Exit sub if no comment exists in active cell.
If cellComment Is Nothing Then
Exit Function
End If
End If
End If
With cellComment
'AutoSize comment box
.Shape.TextFrame.AutoSize = True
'Exit sub if comment box width is shorter than max
If .Shape.Width < maxWidth Then
Exit Function
End If
'textArray becomes string array deliminated by Linefeed Characters [Chr(10)]
'or in other words, by each line in comment
textArr = Split(.Text, Chr(10))
'Save upper bound of textArr array
elements = UBound(textArr)
'Set upper bound of textWidthArr array to same as textArr array
ReDim textWidthArr(elements)
For e = 0 To elements
'puts the length of each line from textArr array into textWidthArr array
textWidthArr(e) = Len(textArr(e))
Next e
'Grabs the max line length
maxLine = wf.Max(textWidthArr)
With .Shape
'Calculates the height of each line (adjusted up 1) in points
lineHeight = .Height / (elements + 1)
'Adjust maxLine length (width)
maxLine = wf.RoundDown(maxLine * (maxWidth / .Width), 0) * 0.9
For e = 0 To elements
If textWidthArr(e) = 0 Then
'No width lines need only one line
linesNeeded = linesNeeded + 1
Else
'Wider original lines need more lines
linesNeeded = linesNeeded + wf.RoundUp(textWidthArr(e) / maxLine, 0)
End If
Next e
.Width = maxWidth
.Height = linesNeeded * lineHeight
End With
End With
End Function
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:
Recommended for you