VBA To Size Comment Boxes In MS Excel 2016 For Mac High Sierra?

[BlackPanther]

Can someone show how to set the size all the worksheet comments boxes in MS Excel 2016 to fit their comments?

In MS Excel 2011, I used the following VBA statements. The red font statement generates an error in MS Excel 2016.


Dim Commnt As Comment
For Each Commnt In Application.ActiveSheet.Comments
' Autosize all comment boxes on the sheet
Commnt.Shape.TextFrame.AutoSize = True
Next

[Aussiebear]

Maybe try the following



Sub ResizeAllComments()
Dim xComment As Comment
For Each xComment In Application.ActiveSheet.Comments ' **Crucial for Mac:**
' - Clear existing AutoSize settings to ensure consistent behavior.
' - Set AutoSize to False before manual sizing.
xComment.Shape.AutoSize = False
' Adjust width and height as needed.
' Example:
xComment.Shape.Width = 200
xComment.Shape.Height = 100
Next xComment
End Sub