How to change default size of the comment boxes

Question

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?

Answer 1

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.