[RESOLVED] Excel VBA - Is it possible make a cell comment auto width and height?

[bongobongo]

Hi.

When I insert a cell comment using VBA, is it possible
to make it auto adjust the width and height according to the content?

If one comment consist of let say 10 lines and the longest line is of 45 characters then those values should direct the height and width of the comment...

Any trick to make the size fit the content would be nice.

[Static]

I could not figure out how to autosize it....

Autosize is for a textframe and autofit is for a range...
I think u will have to make a guess really...
In VB u can use TextWidth.. but VBA does not have it.

Range("A5").AddComment
    With Range("A5").Comment
        .Visible = True
        .Text Text:="Static:" & Chr(10) & "test" & Chr(10) & ""
        .Shape.ScaleWidth 1.9, msoFalse, msoScaleFromTopLeft
        .Shape.ScaleHeight 1.8, msoFalse, msoScaleFromTopLeft
    End With

[bongobongo]

Hi and thanks for your reply!

Did some more testing after I read your reply and found this:

If I USE:

.Shape.TextFrame.AutoSize = True

and REMOVE these two lines:

.Shape.ScaleWidth 1.9, msoFalse, msoScaleFromTopLeft
.Shape.ScaleHeight 1.8, msoFalse, msoScaleFromTopLeft

Then it look like working..... supercool
Looks like chr(10) and the longest line dictates the with, which is what I was looking for.

Thanks for your feedback

[Static]

ahhh.. good catch.. TextFrame cool