Excels Standard Controls

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

Excel has two types of controls. Figure 14-3 shows two toolboxes, each of which provides access to one type of control. (Below each toolbox is a control created using that toolbox.)

Control Toolbox

Figure 14-3. Control toolbars

Control Toolbox






us IB


B ±



m m f. 1 4

CamrriùrtdB ftonl

Button 1

The controls on the Control Toolbox (on the left in Figure 14-3) are ActiveX controls. These controls can be placed either on a UserForm or directly on a worksheet (but not a chartsheet). They are the same as the controls that are accessible from the VB editor's Toolbox when designing a UserForm. ActiveX controls are very flexible and generally support a wide range of events. The Control Toolbox can be opened from within Excel (not the Excel VBA IDE) by selecting the Customize option from the Tools menu and checking the Control Toolbox toolbar in the Toolbars tab.

Note that the Control Toolbox in Figure 14-3 is not the same as the Toolbox in Figure 14-1, even though both are used to access ActiveX controls. The Toolbox in Figure 14-1 places ActiveX controls on user forms; the Control Toolbox in Figure 14-3 places ActiveX controls on worksheets The first button on the Control Toolbox, called the Design Mode button, is particularly important. Pressing it puts the worksheet in design mode at least with respect to its controls. When in design mode, we can move and resize the controls on the worksheet using the mouse. We can also right-click the control to bring up a dialog box with control options. When the Design Mode button is not depressed, clicking on a control with the mouse simply fires the Click event!

By selecting the Customize option from the Tools menu and checking the Forms toolbar in the Toolbars tab, you open the Forms toolbox. The controls on the Forms toolbox (on the right in Figure 14-3) are referred to as "standard Excel worksheet controls" and are a remnant from Excel 5.0. They can be placed on worksheets or chartsheets (but not UserForms) and have only a single event: the Click event.

For instance, if you place a standard button on a worksheet, Excel immediately opens the Assign Macro dialog box, as shown in Figure 14-4. This allows you to assign a macro to the button's Click event.

Was this article helpful?

0 0

Post a comment