Getting an error message "Cannot shift objects off of sheet"

[Guest]

While horizontally grouping (outlining) a set of columns, I receive an error
message that reads "Cannot shift objects off of sheet" when I press the ( - )
to collapse the columns. There are no objects that I am aware of, and the
message does not appear when the last column is ungrouped. Also, it appears
when the previous group is collapsed and visa versa. That is, I can collapse
one group and get the message when I try to collapse the other group. It is
really strange the message pops up at some of the oddest times.

Has anyone encountered this before. Anyone's help would be greatly
appreciated.

Thanks.

Carey

 

[Dave Peterson]

maybe this'll help:

XL: "Cannot Shift Objects Off Sheet" Error Hiding Columns
http://support.microsoft.com/default.aspx?scid=kb;en-ca;211769

(Look for runaway comments!)

 

[Guest]

Dave:

Thanks for the quick response. However, the link did not fully resolve the
problem because there are no objects nor any cell comments. The message shows
up randomly for not reason that I am capable of determining.

For instance, I had created a few groups, open them, then I created new
groups, and was able to collapse the new groups. However, the previously
created groups cannot collapse and I get the same message. It is really go me
baffled.

Carey

 

[Guest]

As an added note:

I am able to create and collapse groups up to a certain cell in the
worksheet without getting the error message. After that, I get the message
for groups I newly create.

Anyone with any ideas of what is going would be apprecated. I definitely
need help on this one. It has got me going in circles.

Carey

 

[Dave Peterson]

How about
edit|goto|special
check objects.

Did anything get selected?

 

[Guest]

Nope, nothing was selected or found. The sheets are free of objects and
comments.

I also copy pasted to a new workbook file. That didn't help either. Around
the G? columns I get the messages when recreating the groups.

Any other ideas? This is becoming very frustrating.

Carey

 

[Dave Peterson]

Sorry, I don't have any more guesses.

Nope, nothing was selected or found. The sheets are free of objects and
comments.

I also copy pasted to a new workbook file. That didn't help either. Around
the G? columns I get the messages when recreating the groups.

Any other ideas? This is becoming very frustrating.

Carey

 

[Guest]

Thanks for your help.

Sorry, I don't have any more guesses.

 

[Norman Jones]

Hi Carey,

If Dave is out of guesses, chances are bleak indeed

However, a possible, 'clutching at straws' idea:

In the course of this thread, you have been adamant that there are no
comments and no objects on your sheet.

How have you checked? How do you *know*?

If, for example, you relied on F5 (Edit | Goto) | Special, this would fail
if there were hidden objects. So if there were hidden shapes (pictures,
drawing objects , checkboxes, text boxes etc etc) and no visible objects,
the F5... method would report "No objects found"

If, therefore, you have not tried a more rigorous method, can I suggest that
you try running the following simple macro:

In tests

'=====================>>

Sub CleanUp()

Dim cmt As Comment
Dim shp As Shape
Dim ws As Worksheet

Set ws = ActiveSheet

On Error Resume Next
ws.Cells _
.SpecialCells(xlCellTypeComments).Delete
On Error GoTo 0

For Each shp In ws.Shapes
shp.Delete
Next

End Sub

'<<=====================

It is very likely that it will not help, but with options running low,
perhaps it is worth trying .

---
Regards,
Norman

Thanks for your help.

 

[Norman Jones]

Hi Carey,

Ignore the extraneous, floating:

In tests

 

[Jon Peltier]

You get a similar error if you try to shift merged cells off the sheet.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Thanks for your help.

:

 

[Dave Peterson]

Thanks, Jon.

Another reason to hate merged cells <bg>.