# Vba comments



## doriannjeshi (Dec 20, 2022)

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 (Dec 20, 2022)

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


----------



## doriannjeshi (Dec 20, 2022)

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 (Dec 20, 2022)

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

For Each cmt In Selection*.Comments*


----------



## doriannjeshi (Dec 21, 2022)

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 (Dec 21, 2022)

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 (Dec 21, 2022)

It works fine Rory , thank you!


----------

