Resize excel comments to fit text with specific width

Question

I'd like to have the comment box fit the comments just right (no extra space at the bottom).

I know there is the .AutoSize but I want the maximum width to be 300.

Here is the code I have,

For Each mycell In myRng.Cells
    If Not (mycell.Comment Is Nothing) Then
      With mycell.Comment.Shape
        .TextFrame.AutoSize = True
        If .width > 300 Then
          lArea = .width * .height
          .width = 300
          .height = (lArea / 300)
        End If
      End With
    End If
Next mycell

mycell and myRng are Range datatypes, lArea is Long.

Now, this works relatively well but leaves extra space at the bottom of a number of comments because the area the AutoSized text takes up is different from the area of the AutoSized comment box.

Is there a way to check for blank space inside a comment and then trim it? Or is what I have the best it is going to be?

Answer 1

try this ... test comment has been placed in cell E4

discovered by putting Range("e4").Comment.Shape.TextFrame in the Watch window

Sub testComment()

    With Range("e4").Comment.Shape

        .TextFrame.AutoSize = True

        lArea = .Width * .Height

        .Width = 300
        .Height = (lArea / .Width)       ' used .width so that it is less work to change final width

        .TextFrame.AutoMargins = False
        .TextFrame.MarginBottom = 0      ' margins need to be tweaked
        .TextFrame.MarginTop = 0
        .TextFrame.MarginLeft = 0
        .TextFrame.MarginRight = 0
    End With
End Sub

Answer 2

I've changed the code in the previous comment to only resize the box if width is above 300 because otherwise the final size of small boxes were messed up. Also changed to go through all comment box on activesheet

Sub reset_box_size()
Dim pComment As Comment
For Each pComment In Application.ActiveSheet.Comments
    With pComment.Shape

        .TextFrame.AutoSize = True

        lArea = .Width * .Height
        
        'only resize the autosize if width is above 300
        If .Width > 300 Then .Height = (lArea / .Width)       ' used .width so that it is less work to change final width

        
        .TextFrame.AutoMargins = False
        .TextFrame.MarginBottom = 0      ' margins need to be tweaked
        .TextFrame.MarginTop = 0
        .TextFrame.MarginLeft = 0
        .TextFrame.MarginRight = 0
        End With
Next

End Sub