Recently, while working on a small worksheet in Excel 2007, I tried to insert a new row. Up popped an annoying error message, “Cannot shift objects off sheet.” I couldn't insert a new row, and had to drag things around, to get the new data inserted.
Keep reading, to see how I investigated the problem, and fixed it with a simple keyboard shortcut.
Change the Object Properties
In older versions of Excel, this problem usually occurred if you tried to insert columns and there was a comment hidden in one of the columns to the right. To fix the problem, you could change the properties of the comment box, so it would move with the cells.
View a List of Objects
It was odd – as far as I knew, the worksheet didn’t have any objects or comments, and only a few rows. What was causing the problem? Maybe there were some hidden shapes that I’d added, and forgotten about, or a comment tucked in a dark corner.
To see what's on a sheet in Excel 2007, you can use the Selection and Visibility Pane.
- Click the Home tab on the Ribbon.
- At the far right, click Find & Select, then click Selection Pane.
It lists all the visible and hidden shapes on the worksheet, including comments, and a data validation dropdown. Visible objects have an eye icon at the right, and hidden objects have an empty icon.
In this example, there’s a comment, data validation drop down, and a Bevel shape on the worksheet.
A Fix For Excel 2007
However, in my problem workbook the Selection Pane list was empty. The old Properties fix wouldn’t work, because there were no objects to change. Fortunately, a search of MSKB turned up an article with the solution:
http://support.microsoft.com/kb/211769
Method 1 solved the problem for me – in Excel Options, change the workbook Display setting for objects, from Nothing (hide objects) to All.
The keyboard shortcut to toggle this setting is Ctrl + 6 – much quicker than opening the Excel Options window.
Problem solved, and I’ve had no more problems inserting rows.
Working with Shapes and Objects
For more information on working with Excel Shapes, and some of the problems you can encounter, visit the Delete or Hide Objects/Controls on a worksheet page on Ron de Bruin’s web site.
________________________
