Autosize textframe

[Jos Vens]

Hi,

I have a problem of autosizing my texframe of a comment (see code below)

I set reference to the cell I want to add comment, but if text is too long,
I can't see it because the rectangle which displays the comment is too
small. Since my sheet is protected, I cannot resize the frame. I use the
autosize-property to give the comment the right size, but somethimes (most
of the times when the textframe is to small) I does not work right,
sometimes, the frame fits the comment.

When I use the procedure in a new worksheet is does the job well. Can I set
the dimensions of the comment procedurally?

Thanks
Jos Vens

set vCell = ActiveCell
vComment = "Hi there, this text is so long it doesn't fit the rectangular
comment box"

vCell.AddComment vComment
vCell.Comment.Shape.TextFrame.AutoSize = True

 

[Leith Ross]

Hello Jos,

This code segment will allow you to resize the Comment. The size i
specified in Points (Single Precision). For the Width property
positive value will increase the Horizontal Size of the Comment, and
negative wiil decrease the size. The Height property is similar
Positive increases the Comment size Vertically, and a Negative valu
decreases the size. I'll leave the Text sizing algorithm in you
hands.

EXAMPLE OF SETTING COMMENT SIZE

Code

 

[Ian]

If the only issue is with the sheet being protected, then try this.

Worksheets("sheetname").Unprotect
---your code here---
Worksheets("sheetname").Protect

 

[Jos Vens]

Thanks Leith,

any idea why the autosize doesn't work (my sheet is unprotected when I
autosize)?

Jos

"Leith Ross" <[email protected]>
schreef in bericht
news:[email protected]...

 

[Leith Ross]

Hello Jos,

Think of a TextFrame object as a Label control. It is placed on top o
another "container" object, like a UserForm for example. A typica
drawing object in Excel can be "filled" with colors, patterns, an
effects, It would be a graphics nightmare to workaround text in th
Drawing. Enter the the TextFrame. A separate object to placce on top o
the ornate Drawing Object, A Comment on the other hand, is a stylis
Label Control.

Hope that explains it,
Leith Ros

 

[Tom Ogilvy]

Autosize will work better if you help it

Change your code to:

Sub abc()
Set vCell = ActiveCell
vComment = "Hi there, this text is so" & Chr(10) & _
"long it doesn't fit the" & Chr(10) & _
"rectangular comment box"

vCell.AddComment vComment
vCell.Comment.Shape.TextFrame.AutoSize = True

End Sub


does that help?

--
Regards,
Tom Ogilvy

 

[Jos Vens]

Hi Tom,

I can't see much difference between your code and mine, except that you
place some chr(10) in between. I cannot ask my users to do this, but even if
I do it procedurally, it doesn't change much. What do you exactly mean by
"help the autosize property"?

The problem is, it works fine in a simple new workbook, but not in my
predefined, prestylished, protected worksheet my user works in. For sure, I
unprotect to add the comment, but the yellow rectangle which shows the
comment has a predefined size (sometimes different, but for all cells the
same size), even when I use the autosize property.

If the autosize works well, it takes the size of the comment (for each
comment a different size)

Thanks anyway, more suggestions?
Jos

 

[Jos Vens]

Hi,

I guess I found the reason why my autosize does not work well:

The cell where I put the comment is in a cell, close to the right border of
the visible area of my sheet. If text is too long, (this means when I
surpasses the visible area) the autosize crashes and a standard height and
width is given by excel I guess.

Is there a workaround? Can I e.g. let it flip to the left side of the screen
in stead of the right side (which is close to the end of the sheet)?

Thanks
Jos