Help - Cannot shift objects off sheet problem

[sedelson@no]

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?

 

[Guest]

Go to Search For (above everyone's questions), and key in "cannot shift
objects". There are a lot of people with the same question, and you should
be able to find a solution.

HTH,
Carole O

 

[Gord Dibben]

Read here for info.

http://support.microsoft.com/default.aspx?scid=kb;en-us;211769


Gord Dibben Excel MVP

 

[sedelson@no]

Thanks for the help, but it doesn't work. I took a section of 15
columns of the spreadsheet and "cleared all" . I then tried resizing
just one column in the middle and I still got the message. That column
and several to the right and left were cleared of everything, but I
still got the "cannot shift objects" messager. There were no objects,
I cleared everything. Any thoughts? Thanks.










=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?

 

[sedelson@no]

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.







=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?

 

[Nick Hodge]

'Clearing' is not deleting..see here

http://www.nickhodge.co.uk/tipstrick.htm#UsedRangeReset

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Gord Dibben]

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