Adding ActiveX Controls to a Form

Like the Worksheet object, the UserForm object is a container object, meaning it is used to hold other objects. When a form is added to a project, the Control Toolbox should automatically appear (see Figure 6.3). If the Control Toolbox does not appear, select View/Toolbox from the menu bar. There will be a few additional controls displayed in the control toolbox when viewed with a form (relative to a worksheet), including the MultiPage and Frame controls (discussed later).

ActiveX controls are added to a form in the same manner that they are added to worksheets. When added to a form, you access the properties of an ActiveX control via the Properties window and you access event procedures associated with ActiveX controls via the form module that contains them. To practice using ActiveX controls on forms, open Excel and from the VBA IDE, insert a form into a new VBA project. Adjust the size properties (Width and Height) of the UserForm object and change its Caption property to "Hello". Add Label and Command Button controls to the form and change their Name properties to something meaningful (for example, lblOutput, and cmdHello). Also, adjust the size and appearance properties of the Label and Command Button controls to suit your taste. Next, double-click on the Command Button control to access its Click() event procedure in the code module of the UserForm object and add one line of code such that the entire procedure appears as follows:

Private Sub cmdHello_Click()

lblOutput.Caption = "Hello!" End Sub

The form, as viewed at Design Time, from the VBA IDE is shown in Figure 6.5.

Run Sub/UserForm button

The UserForm object caption

Label control

Command Button control

A sample form at Design Time.

When the user clicks the Command Button control name cmdHello, the preceding procedure is triggered, and the Caption property of the Label control named lblOutput is changed.

To test the application, select the form and click on Run/Sub UserForm from the IDE standard toolbar (see Figure 6.5) or menu bar, or press F5 on the keyboard. The form appears as a window above the Excel application. Click the Command Button control to output the simple message to the Label control. To close the form, click on the X in the upper-right corner of the window.

Was this article helpful?

+1 0
Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook


Post a comment