Handling Multiple UserForm 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 CommandButton1 or CommandButton2 executes the ButtonClick procedure, and the single argument tells the ButtonClick procedure which button was clicked:

 Private Sub CommandButton1_Click()      Call ButtonClick(1) 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-ROM  

This example, named  multiple buttons .xlsm , is available on the companion CD-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()     UserForm1.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.

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 UserForm1.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.

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