resize Comments : Cell Comments « Excel « VBA / Excel / Access / Word

resize Comments

Sub CommentFitter1()
    Application.ScreenUpdating = False
    Dim x As range, y As Long
    For Each x In cells.SpecialCells(xlCellTypeComments)
        Select Case True
            Case Len(x.NoteText) <> 0
                With x.Comment
                    .Shape.TextFrame.AutoSize = True
                    If .Shape.Width > 250 Then
                        y = .Shape.Width * .Shape.Height
                        .Shape.Width = 150
                        .Shape.Height = (y / 200) * 1.3
                    End If
                End With
        End Select
    Next x
    Application.ScreenUpdating = True
End Sub


Related examples in the same category

1.Get the comment text
2.Converts docstring column into comments on name column.
3.Tracking cell changes in a comment
4.Clean the comment
5.Count Comments
6.Toggle Comments