Vba comments

doriannjeshi

Active Member
Joined
Apr 5, 2015
Messages
301
Office Version
  1. 365
Platform
  1. Windows
Hello,
I need to modify this macro to apply to all comments in the active SELECTION please:

VBA Code:
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
 
Last edited by a moderator:

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Replace this:
For Each cmt In ActiveSheet.Comments
with this:
For Each cmt In Selection
 
Upvote 0
I m doing sth wrong because it says Mismatch

VBA Code:
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
 
Last edited by a moderator:
Upvote 0
Sorry, I didn't give you the correct replacement. Try:

For Each cmt In Selection.Comments
 
Upvote 0
it gives this message:
object doesn't support this property or method!

VBA Code:
 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
 
Last edited by a moderator:
Upvote 0
Try something like this:

VBA Code:
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
 
Upvote 0
Solution

Forum statistics

Threads
1,223,893
Messages
6,175,248
Members
452,623
Latest member
cliftonhandyman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top