XL97: How to Control Object Placement in Microsoft Excel 97

• Microsoft Excel 97 for Windows

SUMMARY

In Microsoft Excel 97, you can control how charts, drawing objects, AutoShapes, controls, and other objects contained in a worksheet are attached to the cells below them. This article explains how to set the Placement property for objects in your worksheets.

MORE INFORMATION

Objects in worksheets can be attached to the cells below them using three different placement styles:

   Placement                        Placement
   Style                            Value       xlConstant
   -----------------------------------------------------------
   Move and size with cells         1           xlMoveAndSize
   Move but don't size with cells   2           xlMove
   Don't move or size with cells    3           xlFreeFloating

Using the Format Dialog Box to Control Placement

To format an object's placement style, follow these steps:

1. Select the object.

2. On the Format menu, click the first item. The name of the item corresponds to the type of object that is selected: for example, AutoShape, Control, Selected Chart Area, and so on.

3. Select the Properties tab.

4. In the Object Positioning section, click the option button for the placement style that you want to use.

5. Click OK.

Using the Properties Window to Control Placement

This method applies only to controls contained in a worksheet. You cannot use this method to format the placement style of other types of objects.

1. Using the right mouse button (right-click), click the control. On the shortcut menu, click Properties.

2. In the Properties window, double-click Placement. The number to the right of Placement will be selected.

3. Type the appropriate placement value (shown in the table above) for the control. Then, press ENTER.

If you set an invalid Placement value, you receive the following error message:

   Unable to set the Placement property of the OLEObject class

Using Visual Basic Code to Control Placement

You can use the Placement Property in Visual Basic for Applications to control the placement style of an object. Here are some examples of how to do this:

   'A control.
   ActiveSheet.Shapes("CommandButton1").Placement = xlMoveAndSize

   'An embedded chart.
   ActiveSheet.ChartObjects("Chart 4").Placement = xlMove

   'A drawing object or AutoShape.
   ActiveSheet.Shapes("Rectangle 3").Placement = xlFreeFloating

REFERENCES

For more information about object placement in Microsoft Excel 97, click the Office Assistant, type the words "object placement", without quotation marks, and press ENTER. Then, click the button labeled "Prevent objects from moving and sizing with cells".