Vba comments

[doriannjeshi]

Hello,
I need to modify this macro to apply to all comments in the active SELECTION please:

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

 

[JoeMo]

Replace this:
For Each cmt In ActiveSheet.Comments
with this:
For Each cmt In Selection

 

[doriannjeshi]

I m doing sth wrong because it says Mismatch

Sub reset_box_size()
 Dim lArea  As Long, h As Long, n As Long
    Dim cmt    As Comment                         '<- added
    For Each cmt In Selection                     '<- 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
End Sub

 

[JoeMo]

Sorry, I didn't give you the correct replacement. Try:

For Each cmt In Selection.Comments

 

[doriannjeshi]

it gives this message:
object doesn't support this property or method!

 Dim lArea  As Long, h As Long, n As Long
    Dim cmt    As Comment                         '<- added
    For Each cmt In Selection.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
End Sub

 

[RoryA]

Try something like this:

Sub reset_box_size()
   Dim lArea As Long, h As Long, n As Long
   Dim commentRange As Range
   On Error Resume Next
   If Selection.Cells.CountLarge > 1 Then
      Set commentRange = Selection.SpecialCells(xlCellTypeComments)
   Else
      If Not Selection.Comment Is Nothing Then Set commentRange = Selection
   End If
   
   If Not commentRange Is Nothing Then
      Dim cell As Range
      For Each cell In commentRange.Cells
         With cell.Comment '<- 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 cell
   End If
End Sub

 

[doriannjeshi]

It works fine Rory , thank you!