Handle Multiple Userform Buttons With One Subroutine
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.
- Create your UserForm as usual, and add several CommandButtons.
- 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
- 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.
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
- 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.
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks