5

It's incredibly frustrating to resize the box every time I put a comment. Anybody know the shortcut, or can provide a clue so I can script this?

1
  • There's this link, but I'm not sure by what you mean by "comment boxes" Sep 10, 2013 at 15:21

1 Answer 1

2

I use this VBA macro to mass-resize comments when its necessary. As far as I know, you cannot change the default size of comment boxes.

I played with the Worksheet_SelectionChange event to trigger the resizing macro automatically, but that's a terrible idea when it comes to sorting/moving large ranges.

Sub AutoFitComments()
    Application.ScreenUpdating = False
    For Each MyComment In ActiveSheet.Comments
        With MyComment
            .Shape.TextFrame.AutoSize = True
            .Shape.Top = .Parent.Top + 3
            .Shape.Left = .Parent.Offset(0, 1).Left + 3
            .Shape.Placement = xlMove
        End With
    Next
    Application.ScreenUpdating = True
End Sub

Insert the code in your workbook's VBA editor. Press Alt+F8 » Enter to run the macro quickly.

enter image description hereenter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .