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

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

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

--
Ian
--
"Jos Vens" wrote in message
...
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

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
-------------------
With Comment(1).Shape
.Height = 30
.Width = 75
End With

-------------------


Sincerely,
Leith Ros

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=48178

Thanks Leith,

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

Jos

"Leith Ross"
schreef in bericht
...

Hello Jos,

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

EXAMPLE OF SETTING COMMENT SIZE:

Code:
--------------------
With Comment(1).Shape
.Height = 30
.Width = 75
End With

--------------------


Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile:
http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=481785

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

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=48178

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" wrote in message
...
Thanks Leith,

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

Jos

"Leith Ross"
schreef in bericht
...

Hello Jos,

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

EXAMPLE OF SETTING COMMENT SIZE:

Code:
--------------------
With Comment(1).Shape
.Height = 30
.Width = 75
End With

--------------------


Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile:
http://www.excelforum.com/member.php...o&userid=18465
View this thread:
http://www.excelforum.com/showthread...hreadid=481785

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


"Tom Ogilvy" schreef in bericht
...
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" wrote in message
...
Thanks Leith,

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

Jos

"Leith Ross"
schreef in bericht
...

Hello Jos,

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

EXAMPLE OF SETTING COMMENT SIZE:

Code:
--------------------
With Comment(1).Shape
.Height = 30
.Width = 75
End With

--------------------


Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile:
http://www.excelforum.com/member.php...o&userid=18465
View this thread:
http://www.excelforum.com/showthread...hreadid=481785

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



"Jos Vens" schreef in bericht
...
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