Jump to content
groovyPost Forums

Cannot shift objects off sheet Excel 2010


LauraM
Go to solution Solved by Simon H,

Recommended Posts

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

Link to comment
Share on other sites

  • Solution

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 to comment
Share on other sites

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

 

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

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Similar Content

    • By Steve
      Question from Reader - Email Form
       
       
      Hi Tom,
       
      Ouch. I know how it feels losing a days worth of work. The worst part is when you have to recreate what you've already done.
       
      To answer your question, when you found the Entry "OutlookSecureTempFolder" what was the path in the DATA column? I assume you reviewed this post here to find it. It looks like you found the registry key entry so your more than 1/2 way there. Find that key again and you should have the path to your OLK folder and your Excel should be sitting there.
       
      Now the path might be a bit confusing because with Outlook 2007, there actually is "NOT" an OLK folder. For instance, on my XP/Outlook 2007 install, my OLK folder or "Temp Outlook Folder" / "OutlookSecureTempFolder" is:
       
      c:\Documents and Settings\mrgroove\Local Settings\Temporary Internet Files\Content.Outlook\4V32TZLP  
      So if you look on your box you will find what your looking for in the "Content.Outlook" folder. Take a look there and your golden.
       
      More reading can be found here also on the Outlook OLK folder location:
      http://www.groovypost.com/howto/microsoft/outlook/find-the-microsoft-outlook-temporary-olk-folder/
       
      Thnx!
    • By micalhassey
      I want to best solution for recovering Excel sheet password because I don’t remember my Excel file password please any one suggest me to remove my Excel file password….Thanks
    • By shockersh
      Quick question for you groovy excel ppl out there. I'm working with a vendor and need to calculate the discount and/or increase. I know there is a simple excel formula I can use. Just looking for a tip for it.
      Thanks!
    • By Beyond_Life
      My dad wants me to make him a excel sheet with all different formula's in it. No problem, I can make those. But in the sheet, the first cells are used for a name from his client, and the second cell is used for a date.
      Q: Is it possible to make Excel use those to cells to create the filename of the sheet when it saves?
    • By Austin
      Beatrice writes:

      I have a question, I know how to do a data validation drop down but I need the one list with two buttons in the same cell and a range of years.Do you know how to do this?
      I work for the school district and they need a particular calendar. They have a cell where you can list the year but it list whole number with a range of 1999 to 2999 and has two buttons.... one to go up in years and one to go lower... Hope this makes sense
  • Who's Online   0 Members, 0 Anonymous, 8 Guests (See full list)

    There are no registered users currently online

×
×
  • Create New...