Forms Controls and ActiveX Controls in Excel

Quoted from: http://peltiertech.com/WordPress/forms-controls-and-activex-controls-in-excel/

 

There are two types of controls for use in Excel worksheets: Forms controls and ActiveX Controls. The Forms controls aren’t very complicated, and they have been part of Excel for longer (I’m thinking Excel 4, and they were used in Excel 5/95’s dialog sheets) than the Controls Toolbox (Excel 97), so it stands to reason that they’d be more seamlessly integrated. Being newer, the ActiveX controls have richer formatting possibilities. Both can link to cells and ranges in the worksheet. Some advanced users have attributed buggy behavior to the ActiveX controls from the Controls Toolbox. I’ve recently had problems with a buggy slider from the Forms toolbar, but I suspect the parent workbook was corrupt.

There are outward similarities, mostly in appearance, between Excel’s Forms controls and ActiveX controls. The ActiveX controls are actually more similar to the ActiveX controls available within VBA for use on UserForms. In Classic Excel and VBA, Forms and ActiveX controls are available on toolbars, while in Excel 2007, they’ve been moved to the Developer tab of the Ribbon.

Forms Controls

ActiveX Controls

VBA Controls

Forms and ActiveX Controls in Excel 2003

Forms controls can be used on worksheets and chart sheets. Forms controls can also be placed within embedded charts in Classic Excel (though not in Excel 2007). ActiveX controls can only be used on worksheets. ActiveX controls do not work in MacExcel. (Neither does VBA at all in fact, if you’re using MacOffice 2008.) Other Office applications also have appropriate selections of controls, though Excel has the widest selection.

Excel responds to a Forms control after the user finishes interacting with it (i.e., unclicks). Excel responds continuously to an ActiveX control. If a Forms slider control is linked to a cell’s value, the user has to slide the bar on the control, then release it before the cell updates. If an ActiveX ScrollBar is linked to the cell’s value, the cell updates continuously as the user slides the bar along the control.

In general I use the Forms controls, unless I need the continuous response of an ActiveX scrolllbar.

Formatting Controls

Forms controls can be formatted as if they were regular shapes (Excel 95 to Excel 2003 shapes; the new shapes in Excel 2007 are different animals altogether), although the options are not as extensive as for shapes, and vary for different control types. The button dialog (below) is much like that for shapes with various Font, Size, Alignment, and similar tabs, although the button is stuck with its gray color and rectangular shape. A regular shape can be assigned a macro in much the same way, with all the shape’s formatting options.

Also depending on the control, there is a tab for properties specific to the control. For a Drop Down or List Box control, there is a means to select an input range, containing the list of items appearing in the control, and another for a cell link, which is where the selected item in the control is stored.

ActiveX controls have a scaled back Format Control dialog, but a rich formatting environment offered by the Properties window. In addition, some controls have properties which indicate the addresses for List Fill Range and Linked Cell.

Using Controls with VBA Procedures

Forms controls can have macros assigned to them (see Assign a Macro to a Button or Shape). Clicking on the control runs the macro. The Assign Macro dialog appears when a control is first created, or when the corresponding item is selected from the right click menu. Any macros in the active workbook or in any open workbooks appear in this list. The default macro is named like an event procedure (“Click”), but that seems appropriate to the ActiveX Controls.

The ActiveX controls have event procedures that run when they are clicked (see Assign a Macro to an ActiveX Control). These procedures are either inserted in the code modules of the objects (i.e., worksheets) within which they are embedded, or written in class modules. Right click on an ActiveX control and select View Code: The shell of the default procedure is inserted into the code module of the object the control is embedded within (i.e., the worksheet). Enter the code here that should be run when the control is clicked.

Note the two dropdowns at the top of the code module. The left dropdown lists all objects associated with the object, including the object itself (Worksheet) and its embedded controls.

When an object is selected in the left dropdown, the right dropdown shows the events available to that control. This provides the ActiveX controls with much greater functionality than the Forms controls.