VBA Comment Format

Question

I am relatively new to coding with excel and I am stuck on a bit of code.

I want to be able to set a fixed size to the comment boxes which I have been able to do with a single cell no problem, but I am not sure how to do it for the whole sheet.

Here is the what I tried to do but didn’t work. Any help would be much appreciated.

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

Suppose your issue is how to reference all comments in a given 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

Side note: Your (undeclared) variable n isn't used in OP.