Microsoft KB Archive/211769

From BetaArchive Wiki

Article ID: 211769

Article Last Modified on 6/7/2007



APPLIES TO

  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition



This article was previously published under Q211769

For a Microsoft Excel 97 version of this article, see 170081.


For a Microsoft Excel 98 Macintosh Edition version of this article, see 178959.

SYMPTOMS

If you try to hide columns of data in Microsoft Excel, you may receive the following error message:

Cannot shift objects off sheet.

To determine the unique number that is associated with the message that you receive, press CTRL+SHIFT+I. The following number appears in the lower-right corner of this message:

100185

CAUSE

This error message occurs if both of the following conditions are true:

  • 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, and all the columns to the right of the column that contain the object.

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).

Note This problem occurs with most objects that can be inserted into a worksheet, including but 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.

Note also that depending on the location of the column that contains the object, you may receive the error message if you try to hide the column that has the object and all the columns to the right of the column that has the object.

RESOLUTION

To resolve this problem, use one of the following methods.

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

  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. This makes the comment visible.
  2. Move the pointer to the edge of the object until the pointer turns into a white arrow pointer with four small black arrows on the pointer. Click the object to select it.
  3. In Microsoft Office Excel 2003 and in earlier version of Excel, click <object name> on the Format menu. In this menu command, <object name> is the name of the object, such as "Comment" or "AutoShape".


In Microsoft Office Excel 2007, click Format in the Cells group on the Home tab. Then, click Format <object name>.

  1. In the Format dialog box, click the Properties tab.
  2. Click Move and size with cells, and then click OK.
  3. If you want to hide the cell comment again, right-click the cell, and then click Hide Comment.

Perform 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.

Method 2: Change the property on all the objects on the active worksheet

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure. However, they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
Note Running the following macro sets the property that is mentioned in Method 1 for all the objects on the active worksheet. Because this setting causes objects to resize when the rows and the columns that are associated with the object are resized, it can cause unexpected results when it displays the objects on the worksheet if you resize the rows and the columns. Consider this problem before you run the macro in your file.

To change the property on all the comments on the active worksheet, run the following macro.

Sub Test()
Dim s As Shape 
On Error Resume Next
For Each s In ActiveSheet.Shapes 
s.Placement = xlMoveAndSize
Next 
End Sub


Additional query words: XL2000 XL2002 XL2003 XL2007

Keywords: kberrmsg kbpending kbprb KB211769