Handle Multiple UserForm Buttons With One Subroutine
Excel 97 (and later) handles custom dialog box controls much differently that Excel 5/95. With an Excel 5/95 Dialog Sheet, you can explicitly assign a subroutine to execute when the dialog box button is clicked. UserForms are diffrent. A button on a UserForm automatically executes the CommandButtonx_Click subroutine. In other words, a UserForm doesn't allow multiple buttons to execute a single subroutine.
This tip describes a way around this limitation by using a Class Module to define a new class. To see this technique in action, download a demo.
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.
Tip: 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.