Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

How to control the placement of objects

View products that this article applies to.


In Microsoft Excel 2000, 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.

↑ Back to the top

More information

You can attach objects in worksheets 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
You can control how objects are attached to cells by using the Format dialog box, the Properties window, or Visual Basic macro code.

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. Click the Properties tab.
  4. Under Object positioning, click the option 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.

To set the placement style for a control, follow these steps:
  1. Right-click the control. On the shortcut menu, click Properties.
  2. In the Properties window, double-click Placement.

    The number to the right of Placement is selected.
  3. Type the appropriate placement value (shown in the table above) for the control. Then, press ENTER.
NOTE: If the Properties window is already visible, it is not necessary to right-click the control. Just select the control, and then double-click Placement to change its placement style.

If you set an invalid placement value, you receive the following error message:
Unable to set the Placement property of the OLEObject class
In Microsoft Excel 2000, the only valid placement values are 1, 2, and 3.

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

↑ Back to the top


For additional information about getting help with Visual Basic for Applications, click the following article number to view the article in the Microsoft Knowledge Base:
226118 Programming resources for Visual Basic for Applications

↑ Back to the top

Keywords: KB211835, kbhowto

↑ Back to the top

Article Info
Article ID : 211835
Revision : 8
Created on : 1/24/2007
Published on : 1/24/2007
Exists online : False
Views : 374