Posted by: tonyteaching | November 5, 2010

Error message when you try to insert or hide rows or columns in Excel: “Cannot shift objects off sheet”

This error message occurs if the following conditions are true:

  • The option Nothing (hide objects) is selected in the Display options for this workbook section in the Excel Options dialog box.
  • You create an object, such as a cell comment, in any cell in a column.
  • You try to hide the column to the left of the column that contains the object, the column that contains the object, or all the columns to the right side of the column that contains the object. Or, in some cases, you try to insert a row or column in the worksheet.

For example, this problem occurs if you put a cell comment in cell IR1, and then try to hide columns IQ:IS (at the same time).
Additionally, this error message occurs when there are hidden objects on the worksheet.

************** SOLUTION:

Method 1: Temporarily toggle the option from “Nothing (hide objects)” to All

In Excel 2007, temporarily toggle the option from Nothing (hide objects) to All, and then insert the rows or columns as needed. To do this, follow these steps:

  1. In the upper-left corner of the Excel window, click the Microsoft Office button.
  2. At the bottom of the menu, click Excel Options.
  3. Click Advanced from list of options on the left.
  4. Scroll down to the Display options for this workbook section, and then click All under For objects, show:, and then click OK.

Notes To use the keyboard shortcut to toggle this selection, press CTRL+6.

If these steps do not let you hide rows or columns or insert rows or columns in Excel 2007, try the steps in method 2.

Method 2: Change the position property of the object to Move and size with cells

To work around this issue, Excel 2003, Excel 2002, and Excel 2000, change the position property of the object to Move and size with cells. To do this, follow these steps:

  1. If the object is a cell comment, select the cell that contains the comment, right-click the cell, and then click Show Comment or Show/Hide Comments.Note This makes the comment visible.
  2. Move the pointer to the edge of the object until the pointer changes into a white arrow with four small black arrows on the pointer. Then, click the object to select it.
  3. In Excel 2003 and earlier version of Excel, click <object name> on the Format menu.Note In this menu command, <object name> is the name of the object, such as “Comment” or “AutoShape.”

    In Excel 2007, click Format <object name>.

  4. In the Format dialog box, click the Properties tab.
  5. Click Move and size with cells, and then click OK.
  6. If you want to hide the cell comment again, right-click the cell that contains the comment, and then click Hide Comment.

Follow these steps for each object in the affected column as described in the “Cause” section. When you hide the columns, you do not receive the error message.

Note This problem occurs with most objects that can be inserted into a worksheet. This includes but is not limited to cell comments, graphs, drawing shapes, and pictures. Pay special notice to cell comments. By default and unlike most objects, cell comments are hidden and may not be immediately visible.

Also, you may receive the error message if you try to hide the column that has the object and all the columns to the right side of the column that has the object. This depends on the location of the column that contains the object.

Source: http://support.microsoft.com/default.aspx?kbid=211769


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: