Resize comment VBA

[doriannjeshi]

Hello,
I need to modify this macro to apply to all comments the active worksheet:

Dim lArea As Long, h As Long, n As Long
With Range("AB5").Comment
n = WorksheetFunction.RoundUp(Len(.TEXT) / 100, 0)
.Shape.TextFrame.AutoSize = True
h = .Shape.height
If .Shape.Width > 400 Then
.Shape.Width = 400
.Shape.height = h * n
End If
End With

 

[rollis13]

With these changes your macro will cycle all the Comments in the active sheet:

Option Explicit
Sub test()
    Dim lArea  As Long, h As Long, n As Long
    Dim cmt    As Comment                         '<- added
    For Each cmt In ActiveSheet.Comments          '<- added
        With cmt                                  '<- changed
            n = WorksheetFunction.RoundUp(Len(.Text) / 100, 0)
            .Shape.TextFrame.AutoSize = True
            h = .Shape.Height
            If .Shape.Width > 400 Then
                .Shape.Width = 400
                .Shape.Height = h * n
            End If
        End With
    Next cmt                                      '<- added
End Sub

 

[rollis13]

Thanks for the positive feedback, glad having been of some help.