Private Sub SelectionCommentAutoSize()
    Const msgTtl = "Авторазмер и привязка примечаний"

    Dim oldAC As XlCalculation, oldASU As Boolean
    Dim c As Range, r As Range

On Error GoTo ErrHandler

    Set r = CommentedCells(Selection)
    If Selection.Cells.Count <>  1  Then
        If r Is Nothing Then
            MsgBox "К выделенным ячейкам нет примечаний", _
                vbInformation, msgTtl
            Exit Sub
        End If
    ElseIf Selection.Comment Is Nothing Then
        If MsgBox("На всем листе?", vbQuestion + vbOKCancel, _
            msgTtl) <> vbOK Then Exit Sub
    Else
        Select Case MsgBox("Только в этой ячейке (ДА)" _
                & vbCr & "или на всем листе (НЕТ)?", _
                vbQuestion + vbYesNoCancel, msgTtl)
            Case vbYes
                Set r = Selection
            Case vbCancel
                Exit Sub
        End Select
    End If

    oldASU = Application.ScreenUpdating
    If oldASU Then Application.ScreenUpdating = False
    oldAC = Application.Calculation
    If oldAC <> xlCalculationManual _
    Then Application.Calculation = xlCalculationManual
    
    For Each c In r.Cells
        With c.Comment.Shape
            If .Top <= c.Top Or .Top >= c.Top + c.Height _
            Then .Top = c.Top + c.Height /  2 #
            If .Left <= c.Left Or .Left >= c.Left + c.Width _
            Then .Left = c.Left + c.Width /  2 #
            If .Placement <> xlMove Then .Placement = xlMove
            If Not .TextFrame.AutoSize Then .TextFrame.AutoSize = True
        End With
    Next
    
    If oldAC <> xlCalculationManual _
    Then Application.Calculation = oldAC
    If oldASU Then Application.ScreenUpdating = True
FinalInstructions:
    Exit Sub
ErrHandler:
    Application.ScreenUpdating = True
    MsgBox Err.Description, vbCritical, msgTtl & ": ERROR #" & Err
    Resume FinalInstructions
End Sub

Function CommentedCells(Rng As Range) As Range
    Dim r As Range
On Error Resume Next
    Set CommentedCells = Rng.SpecialCells(xlCellTypeComments)
    If Err Then Err.Clear: Set CommentedCells = Nothing
End Function