Group, copy, move, or align controls on a worksheet

After you add Form and ActiveX controls (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.) to a worksheet form, you may want to group, copy, move, or align controls to create a well designed, user friendly form.

What do you want to do?


Group, ungroup, or regroup controls

When you group controls, you combine them so that you can work with them as if they were a single control. For example, you can resize or move all controls in a group (group: A collection of objects that behave as one for the purpose of moving, resizing, or rotating them. A group can be composed of multiple sets of groups.) as a single unit.

 Note   You cannot combine Form controls or ActiveX controls with Drawing Tools or SmartArt Tools objects (such as Shapes and SmartArt graphics) in the same group selection.

Do one of the following:

Group controls

  1. If one or more controls is an ActiveX control, do the following:
    1. Make sure that the Developer tab is available.

ShowDisplay the Developer tab

  1. Click the Microsoft Office Button Button image, and then click Excel Options.
  1. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

 Note   The Ribbon is a component of the Microsoft Office Fluent user interface.

  1. Make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode Button image.
  1. Select the controls that you want to group. For more information, see Select or deselect controls on a worksheet.
  2. Right-click the selection, point to Grouping, and then click Group.

Ungroup controls

  1. If one or more controls is an ActiveX control, do the following:
    1. Make sure that the Developer tab is available.

ShowDisplay the Developer tab

  1. Click the Microsoft Office Button Button image, and then click Excel Options.
  1. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

 Note   The Ribbon is a component of the Microsoft Office Fluent user interface.

  1. Make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode Button image.
  1. Select the group that you want to ungroup. For more information, see Select or deselect controls on a worksheet.
  2. Right-click the selection, point to Grouping, and then click Ungroup.
  • To continue ungrouping, click Yes when the message box appears.
  • To change an individual control, continue to select and ungroup controls until the one that you want becomes available.

Regroup controls

  1. If one or more controls is an ActiveX control, do the following:
    1. Make sure that the Developer tab is available.

ShowDisplay the Developer tab

  1. Click the Microsoft Office Button Button image, and then click Excel Options.
  1. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

 Note   The Ribbon is a component of the Microsoft Office Fluent user interface.

  1. Make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode Button image.
  1. Select any one of the controls that was previously grouped. For more information, see Select or deselect controls on a worksheet.
  2. Right-click the selection, point to Grouping, and then click Regroup.

Top of Page Top of Page

Copy one or more controls

You can copy one or more controls in several ways.

 Note   You cannot combine Form controls or ActiveX controls with Drawing Tools or SmartArt Tools objects (such as Shapes and SmartArt graphics) in the same group selection.

By using the Copy and Paste commands

  1. If one or more controls is an ActiveX control, do the following:
    1. Make sure that the Developer tab is available.

ShowDisplay the Developer tab

  1. Click the Microsoft Office Button Button image, and then click Excel Options.
  1. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

 Note   The Ribbon is a component of the Microsoft Office Fluent user interface.

  1. Make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode Button image.
  1. Select the control or controls. For more information, see Select or deselect controls on a worksheet.
  2. To make a single duplicate of a control, on the Home tab, in Clipboard group, click Copy, Button image and then click Paste Button image.

To make multiple duplicates, repeat pasting until you have the number of copies that you want.

By using the keyboard and the mouse

  1. If one or more controls is an ActiveX control, do the following:
    1. Make sure that the Developer tab is available.

ShowDisplay the Developer tab

  1. Click the Microsoft Office Button Button image, and then click Excel Options.
  1. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

 Note   The Ribbon is a component of the Microsoft Office Fluent user interface.

  1. Make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode Button image.
  1. Select the control or controls. For more information, see Select or deselect controls on a worksheet.
  2. Press CTRL and drag the control.

To make multiple duplicates, repeat dragging until you have the number of copies that you want.

Top of Page Top of Page

Move one or more controls

You can move one or more controls in several ways.

 Note   You cannot combine Form controls and ActiveX controls with Drawing Tools or SmartArt Tools objects (such as Shapes and SmartArt graphics) in the same group selection.

By using the mouse

  1. If one or more controls is an ActiveX control, do the following:
    1. Make sure that the Developer tab is available.

ShowDisplay the Developer tab

  1. Click the Microsoft Office Button Button image, and then click Excel Options.
  1. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

 Note   The Ribbon is a component of the Microsoft Office Fluent user interface.

  1. Make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode Button image.
  1. Select the control or controls. For more information, see Select or deselect controls on a worksheet.
  2. Position the pointer over the control until the pointer changes to a cross pointer, and then drag the selected control or controls to another location.

By using the keyboard

  1. If one or more controls is an ActiveX control, do the following:
    1. Make sure that the Developer tab is available.

ShowDisplay the Developer tab

  1. Click the Microsoft Office Button Button image, and then click Excel Options.
  1. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

 Note   The Ribbon is a component of the Microsoft Office Fluent user interface.

  1. Make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode Button image.
  1. Select the control or controls. For more information, see Select or deselect controls on a worksheet.
  2. Press the arrow keys to move the control in single pixel (pixel: A single unit of measurement that your computer's display hardware uses to paint images on your screen. These units, which often appear as tiny dots, compose the pictures displayed by your screen.) increments.

Top of Page Top of Page

Move a control forward or backward in the object stacking order

Controls and other objects on the drawing canvas (drawing canvas: An area on which you can draw multiple shapes. Because the shapes are contained within the drawing canvas, they can be moved and resized as a unit.) automatically stack in individual layers as you add them. You can see the stacking order when controls overlap — the top control partially covers the controls underneath it.

 Note   Form and ActiveX controls have an object stacking order that is separate and distinct from the Drawing Tools and SmartArt Tools objects, such as Shapes and SmartArt graphics.

  1. If one or more controls is an ActiveX control, do the following:
    1. Make sure that the Developer tab is available.

ShowDisplay the Developer tab

  1. Click the Microsoft Office Button Button image, and then click Excel Options.
  1. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

 Note   The Ribbon is a component of the Microsoft Office Fluent user interface.

  1. Make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode Button image.
  1. Select the control. For more information, see Select or deselect controls on a worksheet.
  2. Right-click the selection, point to Order, and then do the following:
  • To move the control one layer up in the stacking order, click Bring Forward.
  • To move the control to the top layer in the stacking order, click Bring to Front.
  • To move the control one layer down in the stacking order, click Send Backward.
  • To move the control to the bottom layer in the stacking order, clickSend to Back.

Top of Page Top of Page

Align and distribute controls

 Important   To do the following procedures, you must add the Align button menu on the Drawing Tools tab to the Quick Access Toolbar.

ShowAdd the Align button menu from the Drawing Tools tab to the Quick Access Toolbar

  1. Click the arrow next to the Quick Access Toolbar, and then click More Commands.
  2. Under Choose commands from, select All Commands.
  3. In the list, select Align Button image.
  4. Click Add, and then click OK.

 Note   You cannot combine Form controls and ActiveX controls with SmartArt Tools objects (such as Shapes and SmartArt) in the same group selection.

Align all controls to the grid or to shapes

Do one of the following:

  • To align all objects with the upper-left corner of a cell, on the Quick Access Toolbar, click the arrow next to Align Button image, and then click Snap to Grid Button image.

 Note   The Snap To feature works whether gridlines are turned on or off. You can also align controls with cell gridlines by holding the ALT key while you move a control or object.

  • To automatically align controls with the vertical and horizontal edges of other shapes when you move or draw them, on the Quick Access Toolbar, click the arrow next to Align Button image, and then click Snap to Shape Button image.

Align controls by their edges

 Important   Aligning controls can cause them to stack on top of each other. Make sure that the controls are positioned relative to each other in the way that you want before you run a command to align them.

  1. If one or more controls is an ActiveX control, do the following:
    1. Make sure that the Developer tab is available.

ShowDisplay the Developer tab

  1. Click the Microsoft Office Button Button image, and then click Excel Options.
  1. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

 Note   The Ribbon is a component of the Microsoft Office Fluent user interface.

  1. Make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode Button image.
  1. Select the controls. For more information, see Select or deselect controls on a worksheet.
  2. To align the controls, on the Quick Access Toolbar, click the arrow next to Align Button image, and then do one of the following:
  • To align the controls by the top-most control, click Align Top Button image.
  • To align the controls by the bottom-most control, click Align Bottom Button image.
  • To align the controls by the left-most control, click Align Left Button image.
  • To align the controls by the right-most control, Align Right Button image.

Align controls horizontally or vertically

  1. If one or more controls is an ActiveX control, do the following:
    1. Make sure that the Developer tab is available.

ShowDisplay the Developer tab

  1. Click the Microsoft Office Button Button image, and then click Excel Options.
  1. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

 Note   The Ribbon is a component of the Microsoft Office Fluent user interface.

  1. Make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode Button image.
  1. Select the controls. For more information, see Select or deselect controls on a worksheet.
  2. To align the controls, on the Quick Access Toolbar, click the arrow next to Align Button image, and then do one of the following:
  • To align controls horizontally through the middle of the controls, click Align Middle Button image.
  • To align controls vertically through the centers of the controls, click Align Center Button image.

Distribute controls horizontally or vertically

  1. If one or more controls is an ActiveX control, do the following:
    1. Make sure that the Developer tab is available.

ShowDisplay the Developer tab

  1. Click the Microsoft Office Button Button image, and then click Excel Options.
  1. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

 Note   The Ribbon is a component of the Microsoft Office Fluent user interface.

  1. Make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode Button image.
  1. Select at least three controls. For more information, see Select or deselect controls on a worksheet.
  2. To distribute the controls, on the Quick Access Toolbar, click the arrow next to Align Button image, and then do one of the following:
  • To evenly space controls horizontally, click Distribute Horizontally Button image.
  • To evenly space controls vertically, click Distribute Vertically Button image.

Top of Page Top of Page

 
 
Applies to:
Excel 2007