login about faq

I am trying to hide all the columns from E to the end of the sheet but keep getting the error message cannot shift objects off sheet.

Everyone says this is down to hidden objects in the sheet or hidden comments but I have used the clear all icon to remove everything from the cells that I want to hide but still keep getting the error message.

I have checked the find and select selection pane and there is only one comment on the sheet and that is in cell C3 so nothing to do with that.

I haven't copy and pasted anything into the sheet so its not images as someone else suggested.

Please can anyone help I am beginning to think my Excel is broken.

Laura

asked Jan 30 '13 at 13:09

LauraM's gravatar image

LauraM
20112


This error can be due to objects in the cells you are trying to manipulate but it can also be due to an object overlapping from a nearby row or column as in the image below. The comment is in Cell C3 but the hover over pop up window overlaps onto columns D, E & F. You need to leave enough unhidden space on the spreadsheet to the right of column C otherwise you will get the message cannot shift objects off sheet.

alt text

You can edit the comment and drag the edit box to a new position where it doesn’t overlap the columns that you want to hide, as in the image below. This will allow you to hide all the columns that you want to.

alt text

The problem with this is that, although you can reposition the comment window whist editing, the hover over pop up window will still be anchored in the same place when you mouse over it and consequently with the columns hidden it won’t show properly as in the image below. Unfortunately there is no way to change the default anchor point of the hover over pop up window.

alt text

You could chose to have the comment showing all the time, in which case it will be displayed in the same position as the edit box as in the image below. This could look messy though if you had lots of comments on the sheet

alt text

Another workaround would be to drag the unhidden column so it’s wide enough to display the pop up window correctly as in the image below. This way you can have the comment hidden until its needed.

alt text

The other thing you could do is replace the comment with a data validation message as in the image below. The problem with this would be that the cell needs to be selected for the message to show so you will need to choose what suits your worksheet best.

alt text

link

answered Jan 31 '13 at 05:09

Simon%20H's gravatar image

Simon H
5.0k62683

Cannot shift objects off sheet is now solved by making the column wider.

(Feb 06 '13 at 03:33) LauraM LauraM's gravatar image
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or __italic__
  • **bold** or __bold__
  • link:
    [text](http://url.com/ "title")
  • image:
    ![alt text](/path/img.jpg "title")
  • numbered list:
    1. Foo
    2. Bar
  • Code:
    • Within a paragraph:
      `code`
      surround code with grave accents (backticks)
    • Code snippet:
      Select the text and use the button on the icon of zeros and ones
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×17
×1

Asked: Jan 30 '13 at 13:09

Seen: 21,065 times

Last updated: Feb 06 '13 at 03:33

groovyAnswers © 2007 - 2011 All Rights Reserved | Privacy | Contact