Resize excel comments to fit text with specific width

user_6377101 0
Viewed 11196

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?

comments vba excel
3 Answers

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
user_144128770
Related