Position and size a control with its underlying cell on a worksheet

At times, you may want a control to act independently of its underlying cell (the cell which contains the upper leftmost corner of the control). At other times, you may want the control to move and size with its underlying cell. For example, if you have a check box that is linked with a particular cell or row, you may want that check box to move with the row when the range is sorted. However, if you have a list box that you want to keep in a specific location at all times, you do not want it to move with its underlying cell.

  1. If one or more controls is an ActiveX control:
    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.

  1. Right-click the selection, click Format Control, and then click the Properties tab.
  2. Under Object Positioning, do one of the following:
  • To make the control stay with a cell when the cell moves (for example, when it is sorted) and to resize the control when the cell height and width changes, select Move and size with cells.

 Note   This option is not available for Form controls.

  • To make the control stay with a cell when the cell moves (for example, when the range or table that contains the cell is sorted), but keep the control's size independent of the changes to the cell's height and width, select Move but don't size with cells.
  • To keep the control's position and size independent of the changes to the cell's position, height, and width, select Don't move or size with cells.

 Notes 

  • The underlying cell is determined by the location of the upper leftmost corner of the control.
  • Make sure that Cut, copy, and sort inserted objects with their parent cells option is set in the Excel Options dialog box. Click the Microsoft Office Button Button image, click Excel Options and then, in the Advanced category, under Cut, copy, and paste, select the Cut, copy, and sort inserted objects with their parent cells check box.
  • If you want controls to be sorted and filtered with their underlying cells, for best results, use ActiveX controls and fit the control to the exact height and width of the underlying cell. (Press ALT when you move and size the cell.)
  • A control cannot be moved with its underlying cell if the control is locked and if the worksheet on which it is located is protected. For more information, see the See Also section.
 
 
Applies to:
Excel 2007