Handle Multiple Userform Buttons With One Subroutine

Category: UserForms | [Item URL]

A companion file is available: Click here to download

When you create a UserForm, every control on the form must have its own event handler procedure. For example, if a UserForm has 12 CommandButtons, you need 12 procedures to handle the click events for those buttons.

This tip describes a way around this limitation by using a Class Module to define a new class.

Procedure

1. Create your UserForm as usual, and add several CommandButtons.
2. Insert a Class Module and enter the following code. You will need to customize the ButtonGroup_Click subroutine.

Public WithEvents ButtonGroup As CommandButton

Private Sub ButtonGroup_Click()
    MsgBox "Hello from " & ButtonGroup.Name
End Sub

3. Insert a normal module and enter the following code. In this code, I exclude a button named OKButton from the "button group." Therefore, clicking the OK Button does not execute the ButtonGroup_Click subroutine.

Option Explicit

Dim Buttons() As New Class1

Sub ShowDialog()
    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
            If ctl.Name <> "OKButton" Then 'Skip the OKButton
                ButtonCount = ButtonCount + 1
                ReDim Preserve Buttons(1 To ButtonCount)
               Set Buttons(ButtonCount).ButtonGroup = ctl
            End If
        End If
    Next ctl
    UserForm1.Show
End Sub

4. Execute the ShowDialog subroutine to display the UserForm. Clicking any of the CommandButtons (except the OKButton) executes the ButtonGroup_Click subroutine.

Note: To use this technique with other types of controls, change the WithEvents statement. For example:

   Public WithEvents LabelGroup As MSForms.Label

Notice that you must qualify the Label object because Excel also has an object named Label. Also, you will need to make appropriate changes throughout the ShowDialog procedure.