Excel® 2007 VBA Programmer's Reference

Chapter 10. Adding Controls

As discussed in Chapter 1, you can add two different types of controls to Excel worksheets: ActiveX controls or Form controls. The Form controls originated in Excel 5 and Excel 95 and provide controls for the dialog sheets used in those versions, as well as controls embedded in a worksheet or chart. Dialog sheets have been superseded by UserForms since the release of Excel 97, and UserForms utilize the ActiveX controls.

You can create controls by activating the Developer tab of the Ribbon and clicking the Insert button in the Controls group. This displays the dialog box shown in Figure 10-1.

Figure 10-1. Figure 10-1

Form and ActiveX Controls

The Form controls and dialog sheets are still supported in Excel. Form controls even have some advantages over the ActiveX controls. They are less complex than the ActiveX controls. However, each Form control can only respond to a single event. In most cases, that event is the Click event — the edit box is an exception, responding to the Change event.

If you want to create controls and define their event procedures in your VBA code, as opposed to creating them manually, the Form controls are easier to work with. A big advantage over an ActiveX control is that the event procedure for a Form control can be placed in a standard module, can have any valid VBA procedure name, and can be created when you write the code for the ...