Is there any sort of autosize for Comments

[Goshute]

For some reason, probably hiding columns which the comment text displayed over, a bunch of comment boundaries got minimized. I was able to build this to fix it:

Code: Select all

Sub ReSizeComments()
  Dim intC As Integer
  
  For intC = 1 To ActiveSheet.Comments.Count
    With ActiveSheet.Comments(intC).Shape
      .Height = 60
      .Width = 96
    End With
  Next intC
End Sub

But is there a way to autosize Comments to the text in them?

[rory]

Yep - you need to add:

Code: Select all

.Textframe.Autosize = True

[Goshute]

Nice, but some of my comments are longish and Autosize doesn't text wrap - I have to go in and use Ctrl-Enter to get line breaks to force Autosize with text wrap (Office 2010).

[rory]

How would it autosize otherwise? :)

[Goshute]

Now I gotta write a word wrap function for Comment text. Life is hard.

[Rick Rothstein]

How would it autosize otherwise? :)

I would think it could do it just like a cell with wordwrap enabled does it... autosize the height while retaining the existing (or possibly a specifically specified) width.

[sdckapr]

I would think it could do it just like a cell with wordwrap enabled does it... autosize the height while retaining the existing (or possibly a specifically specified) width.

It could, but unfortunately it does not. Like Text boxes it only has autofit, not autofit ht given width or autofit width given ht. As far as I can see, that functionality would have to be user-created and (as you probably can imagine) it is not a trivial task...

Steve