VBA Comment Format

Question

I'm having trouble with a section of code because I'm a novice at coding in Excel.

I know how to establish a fixed size for a single cell, but I'm not sure how to do it for the entire sheet. I want to be able to set a fixed size for the comment boxes.

Here is what I tried to accomplish, but it wasn't successful. Any assistance would be greatly valued.

Sub com2()
  Dim lArea As Long, h As Long, n As Long
  With Range("ActiveSheet").Comment
    n = WorksheetFunction.RoundUp(Len(.Text) / 100, 0)
    .Shape.TextFrame.AutoSize = True
    h = .Shape.Height
    If .Shape.Width > 250 Then
      .Shape.Width = 250
      .Shape.Height = 250
    End If
  End With
End Sub

Answer 1

Let's say you're having trouble referencing every comment in a specific worksheet.

Sub AutoSizeComments()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Set ws = Tabelle4
    'Loop through comments
    Dim c As Range
    For Each c In ws.Cells.SpecialCells(xlCellTypeComments)
        adaptCommentSize c
    Next c
    Application.ScreenUpdating = True
End Sub

Sub adaptCommentSize(c As Range, _
    Optional ByVal w As Double = 250, _
    Optional ByVal h As Double = 250)
    With c.Comment.Shape
        With .TextFrame
            .AutoSize = True
            .AutoMargins = False
            .MarginBottom = 0
            .MarginTop = 0
            .MarginLeft = 0
            .MarginRight = 0
        End With

        If .Width > w Then
            .Width = w
            .Height = h
        End If

    End With
End Sub