Comment box size (Office 2000)

[dm19134]

Is there a way to set autosize as the default in format comment?

[Wassim]

<img src=/S/hello.gif border=0 alt=hello width=25 height=29> dm19134

OK if you are talking about the size of the comment box on a worksheet, you can use the:

ScaleWidth and
ScaleHeight

of the ShapeRange and set it programmatically with VBA. If you need help with VBA, post a reply and we'll try and help.

Wassim

[dm19134]

No, what I'm looking for is a way to make autosize the default in the comment box format

[sdckapr]

I don't know if you can change the default for new ones (my GUT feeling is that you can NOT), you can set them after the fact with a macro:
While you are changing them "en-mass" you can change other properties to suit if desired that are also NOT "defaultable"

Steve

<pre>Option Explicit
Sub AutoSizeComments()
Dim c As Comment
For Each c In ActiveSheet.Comments
c.Shape.TextFrame.AutoSize = True
Next
End Sub</pre>

[Wassim]

dm19134

Are you talking about the Comment is a worksheet cell, or something else?

What is the AutoSize that you would like to comment to be?

1) This is not an option on the Format Comment. The stuff that deals with size, require you to provide the size, and you have to do that individually.

2) To automate something, you need VBA, unless you want to do the same process over and over and over until all of your comments are sized the way you want them. And you can imagine how tedious and error prone this process is going to be.

3) You have to define what is your preferred size for the AutoSize, this also will need VBA.

4) When you want a feature that is not supported directly by the Interface you will produce it via automation via VBA.

I am not selling you VBA, but your function is not there <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

Wassim

[dm19134]

Thanks everyone (what a resource this place is!). The macro works great, better than mine. Thanks Wassim: you know this stuff.

[sdckapr]

Autosize is a property of the textbox around the comment. if TRUE It will grow/shrink with the text added to it. The default is FALSE.

Manually you can change it via format comment - alignment tab - check "automatic size"

Steve

[macropod]

Hi Steve,

The problem with "automatic size" is that this kills of word wrapping and formats your comment as a single line - sometimes a very long single line. You then have to put in hard line breaks to restore the appearance of wrapping.

So, ideally, you'd need to add some logic to the macro to insert CRs (between words) every so often and, if the comment is likely to be edited, to update the CR placements (which could be tricky to differentiate from para ends. At that point it probably becomes easier to work with a defined width and try to work out the height from the number of auto-wrapped lines. Should be fun! (not)

Cheers

[sdckapr]

You are correct.

I guess I am used to using them with textboxes and datalabels linked to cells with "variable" content that changes size, so I always want the autosize and I will insert CRs after particular line segments in the formulas.

Steve