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 application, before the control is created.

You can create the control programmatically, when it is needed, and assign the procedure name to the OnAction property of the control. You can even assign the same procedure to more than one control. On the other hand, ActiveX event procedures must be placed in the class module behind the worksheet or user form in which they are embedded, and must have a procedure name that corresponds with the name of the control and the name of the event. For example, the click event procedure for a control named OptionButton1 must be as follows:

Sub OptionButton1_Click()

If you try to create an event procedure for an ActiveX control before the control exists, and you try to reference that control in your code, you will get compiler errors, so you have to create the event procedure programmatically. This is not an easy task, as you will see in later sections.

In addition, see Chapter 26 for an example of adding an event procedure programmatically to a UserForm control.

On the other hand, a procedure that is executed by a Form control does not need to have a special name and can use the Caller property of the Application object to obtain a reference to the control that executes it. The control name does not need to be included in the name of the procedure or in references to the control, as you will see later in this chapter.

0 0

Post a comment