VBA to Autosize an Excel note is not working for me on MAC/365 works on Windows/Excel v10

[Mark_Smith_007]

I have tried reinstalling Excel on my MAC to no avail. This is the VBA and it is the 'xComment.Shape.TextFrame.AutoSize = True' statement it objects to. I have wasted a whole afternoon on this and really need some help. Mark

Sub FitComments()
Dim xComment As Comment
For Each xComment In Application.ActiveSheet.Comments
xComment.Shape.TextFrame.AutoSize = True
Next

End Sub

[#2 · Accepted solution]

@Mark_Smith_007 

Mark,

Spent a long time looking at this, gave up a couple of times as being undoable. It appears that on a Mac autosize does not work. I looked at various macros other people had done and ended up butchering one to work with comments. Even then there is still some guess work involved. In a nut shell the Mac copies the comments text, places it into a cell on a temporary sheet, sizes the cell with to the same width as the comment, the calculates how many lines of text there are then multiplies that by the text size in pixels to get the height of the comment.

Not 100% accurate but nearly gets it right.

Rich

[#3]

@Rich99Hi Rich, thanks for this you have got me out of a hole. I needed to resolve this one way or another for next Monday so I am very happy. I will experiment with a bigger data set and see how I get on. Thanks again, Mark

[#4]

Thank you Rich for posting this macro. This helped resize comments in an excel file on a mac. Worked wonderfully! Appreciate your effort!

Timothy