As noted earlier, controls are added to an Excel sheet using the Insert button located on the Developer tab in the Controls group.
Creating a control on a worksheet involves two stages: placing the control on the worksheet and configuring it.
Configuration means setting the control’s properties; in particular, you can link the control to certain worksheet cells, adjust the control’s appearance, and set other parameters.
You can associate a macro with a control that will run when a certain event occurs—for example, when clicking a button (for the Button/CommandButton control)—or you can provide a corresponding procedure written in VBA.
NOTE
In localized versions, the Button control has the same name on both the Form Controls and ActiveX Controls toolbars, but in the non-localized (English) version the names differ (Button vs CommandButton).
To place a control on a worksheet, do the following:
- Click Insert on the ribbon’s Developer tab in the Controls group and, on the drop-down panel, select the appropriate control from one of the groups: Form Controls or ActiveX Controls. The mouse pointer will change to a crosshair.
- Move the pointer to the place on the worksheet where you want to position the control and click the left mouse button. The control will appear on the worksheet.
- Resize the control as desired by dragging the white squares—the control’s border handles (simply called “handles”).
If you need to choose one of the additional controls from a dialog:
- Click More Controls on the ActiveX Controls panel.
- In the list that appears, select the required control. The pointer will change to a crosshair.
- Move the pointer to the desired location on the worksheet and click the mouse. The control will appear on the worksheet.
- If necessary, change the control’s size.
A control is not anchored to a specific spot on the worksheet; it can be freely moved anywhere else. You can move an object with the mouse or the keyboard. The mouse is more convenient for moving longer distances.
To move a control with the mouse:
- Select the required control. To select an ActiveX control, click Design Mode in the Controls group on the Developer tab, and then click the control. The selected control has a border with handles.
- To select a control from Form Controls, move the pointer over it and click the left mouse button while holding down . The selected control has a thick gray border with handles.
- Drag the control to a new location with the mouse. Drag either by the control’s border or by its graphic surface. If you try to drag by the control’s caption, you may enter caption edit mode and won’t be able to drag it.
TIP
To select multiple controls, select each one while holding down +.
To constrain movement strictly vertically or horizontally while dragging, hold . To snap the control to the worksheet grid while dragging, hold . You can combine these effects by holding both keys during the drag.
Moving a control with the keyboard is more convenient when you need precise positioning.
To move a control with the keyboard:
- Select the control you want to move.
- Move it using the arrow keys (←, →, ↑, ↓).
Sometimes you need to make several copies of the same control.
To copy a control:
- Select the required control.
- While holding down , drag the object to the location where you want the copy. When you release the mouse button, a copy of the object will appear at the specified location.
TIP
When copying, you can use and the same way as when moving controls.
Various operations can be performed with controls: you can group them, send them to the back or bring them to the front, anchor them to objects, align them, etc. These actions are performed with controls the same way as with pictures, except for certain specifics during selection.
Controls are objects. Like any other objects, they have properties, methods, and events. Property values of controls can be set in code or during design time. To set properties at design time, select the control and click Properties on the Developer tab in the Controls group. The Properties window will appear.
The left side of this window lists the control’s properties, and the right side contains either input fields or drop-down lists to set those property values.
As mentioned earlier, you can associate a macro or a VBA procedure with a control to run when a particular event occurs. The code of the procedure that handles the control’s events is entered in the worksheet module where the control is located. To go to this module, select the control and click View Code on the Developer tab in the Controls group.
When you finish designing the control, don’t forget to exit Design Mode by clicking Design Mode again.