Handling Multiple User Form Controls with One Event Handler

Every CommandButton on a UserForm must have its own procedure to handle its events. For example, if you have two CommandButtons, you'll need two event handler procedures for the controls' click events:

Private Sub CommandButton1_Click() ' Code goes here End Sub

Private Sub CommandButton2_Click() ' Code goes here End Sub

In other words, you cannot assign a macro to execute when any CommandButton is clicked. Each Click event handler is hard-wired to its CommandButton. You can, however, have each event handler call another all-inclusive macro in the event handler procedures, but you'll need to pass an argument to indicate which button was clicked. In the following examples, clicking either CommandButtonl or CommandButton2 executes the ButtonClick procedure, and the single argument tells the ButtonClick procedure which button was clicked:

Private Sub CommandButton1_Click()

Call ButtonClick(l) End Sub

Private Sub CommandButton2_Click()

Call ButtonClick(2) End Sub

If your UserForm has many CommandButtons, setting up all these event handlers can get tedious. You might prefer to have a single procedure that can determine which button was clicked and take the appropriate action.

This section describes a way around this limitation by using a class module to define a new class.

CD- This example, named multiple buttons .xlsm, is available on the companion cd-

ROM rom.

The following steps describe how to re-create the example UserForm shown in Figure 15-21.

1. Create your UserForm as usual and add several CommandButtons. (The example on the CD contains 16 CommandButton controls.) This example assumes that the form is named

UserForm1.

2. Insert a class module into your project (choose Insert Class Module), give it the name BtnClass, and enter the following code. You will need to customize the ButtonGroup_Click procedure.

Public WithEvents ButtonGroup As MsForms.CommandButton

Private Sub ButtonGroup_Click()

Msg = "You clicked " & ButtonGroup.Name & vbCrLf & vbCrLf Msg = Msg & "Caption: " & ButtonGroup.Caption & vbCrLf

Msg = Msg & "Left Position: " & ButtonGroup.Left & vbCrLf

Msg = Msg & "Top Position: " & ButtonGroup.Top

MsgBox Msg, vbInformation, ButtonGroup.Name End Sub

Tip You can adapt this technique to work with other types of controls. You need to change the type name in the Public withEvents declaration. For example, if you have OptionButtons instead of CommandButtons, use a declaration statement like this:

Public WithEvents ButtonGroup As MsForms.OptionButton

3. Insert a normal VBA module and enter the following code. This routine simply displays the UserForm:

Sub ShowDialog()

UserForml.Show End Sub

4. In the code module for the UserForm, enter the userForm_initialize code that follows. This procedure is kicked off by the UserForm's initialize event. Notice that the code excludes a button named OKButton from the button group. Therefore, clicking the OK button does not execute the ButtonGroup_click procedure.

Class P.lo-dule Uemo m

1

5

9

13

2

e>

id

14

3

7

11

15

4

8

12

IS

Cktjng any cf die si Cort^nd Sum™ (etfjept atO eusaiiea rG" iLU_Cfck J'-rti, nhdtü

r-JiiUhilj. .„djdrtitä i Gefl"-WK3uitori_C it sij?rüLiV> fef tsdi CwfnviÄiicor.

Cktjng any cf die si Cort^nd Sum™ (etfjept atO eusaiiea rG" iLU_Cfck J'-rti, nhdtü

r-JiiUhilj. .„djdrtitä i Gefl"-WK3uitori_C it sij?rüLiV> fef tsdi CwfnviÄiicor.

Figure 15-21: Many CommandButtons with a single event-handler procedure.

Dim Buttons() As New BtnClass

Private Sub UserForm_Initialize() Dim ButtonCount As Integer Dim ctl As Control

Create the Button objects ButtonCount = 0

For Each ctl In UserForml.Controls

If TypeName(ctl) = "CommandButton" Then 'Skip the OKButton If ctl.Name <> "OKButton" Then

ButtonCount = ButtonCount + 1 ReDim Preserve Buttons(1 To ButtonCount) Set Buttons(ButtonCount).ButtonGroup = ctl End If End If

Next ctl End Sub

After performing these steps, you can execute the showDialog procedure to display the UserForm. Clicking any of the CommandButtons (except the OK button) executes the ButtonGroup_click procedure. Figure 15-22 shows an example of the message displayed when a button is clicked.

A.

H

1

j k

Iiatrti

liilr'fhima fcj

1

S

t

u

¡■npnri CiK) rifuin •»

U

^Mrmmri^ttiiMife loarwdBLcion_Qd[ aJrz-Tt fa

t

11

u

4,

i

*

H

f. DmnunHPMiKi11

Figure 15-22: The ButtonGroup_Click procedure describes the button that was clicked.

4 PREV

0 0

Post a comment