To autosize all comments on a worksheet........
Public Sub Comment_Size()
Dim cmt As Comment
Dim cmts As Comments
Set cmts = ActiveSheet.Comments
For Each cmt In cmts
cmt.Shape.TextFrame.AutoSize = True
Next
End Sub
I don't believe that re-sizing Comments will assist with the problem.
Setting the "move and size with cells" property is what eliminates the error
message.
See the methods at the KB article I originally posted.
Text from that article............
Note Running the following macro sets the property that is mentioned in Method
1 for all the objects on the active worksheet. Because this setting causes
objects to resize when the rows and the columns that are associated with the
object are resized, it can cause unexpected results when it displays the
objects on the worksheet if you resize the rows and the columns. Consider this
problem before you run the macro in your file.
To change the property on all the comments on the active worksheet, run the
following macro:
Sub Test()
Dim s As Shape
On Error Resume Next
For Each s In ActiveSheet.Shapes
s.Placement = xlMoveAndSize
Next
End Sub
End article text.........................
Gord
On Wed, 29 Dec 2004 07:19:18 -0500, sedelson@no wrote:
i just tried clearing all comments from the whole spreadsheet and the
problem went away. This is a very large sheet with hundreds of
comments. Is there some way I can universally change their properties
to allow resizing. Doing each one individually would take hours.
Thanks.
On Tue, 28 Dec 2004 15:22:29 -0800, Gord Dibben <gorddibbATshawDOTca
wrotg:
=Read here for info.
=
=http://support.microsoft.com/default.aspx?scid=kb;en-us;211769
=
=
=Gord Dibben Excel MVP
=
=
=On Tue, 28 Dec 2004 15:02:00 -0500, sedelson@no wrote:
=
=I have a spreadsheet that used to work fine. But now when I try to
=reduce the size of a column (or hide it) I get a message "cannot shift
=objects off sheet". There are no objects that I am aware of and it
=happens on every column, even ones that I have changed the width in
=the past. Can anyone help?
|