Handling Multiple User Form Buttons With One Event Handler

Every CommandButton on a UserForm must have its own procedure to handle its Click event. For example, if you have two CommandButtons, you'll need at least two event handler procedures:

Private Sub CommandButtonl_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 both execute the ButtonClick procedure, and the single argument tells the ButtonClick procedure which button was clicked.

Private Sub CommandButtonl_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.

This example is available on the companion CD-ROM.

The following steps describe how to re-create the example workbook:

1. Create your UserForm as usual and add several CommandButtons. (The example on the CD contains 16 CommandButttons.) 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, vblnformation, ButtonGroup.Name End Sub

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 code in Listing 15-4. 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 OKButton does not execute the ButtonGroup_ Click procedure.

Listing 15-4: Establishing the Buttons() Object Array

Dim Buttons() As New BtnClass

Private Sub UserForm_Initialize()


Listing 15-4 (Continued)

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

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 End Sub

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





Clicking any of these Command Buttons (except OK) executes the ButtonGroup_Click subroutine; which is defined in the BtnClass Class Module,

Normally, you would need a separate CommandButton_Click subroutine for each CommandButton,














^^ You clicked CommandButton 10

Caption: 10 Left Position: 87.5 Top Position: 32.25

|i OK ! I


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

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

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

+7 -3


  • fulvus sandheaver
    When i open a user form i wnat cursor to appear in text box?
    7 years ago
  • markus foerster
    How to execute multiple actions with one command button in userform?
    7 years ago
  • topias
    How many userform is usual vba?
    7 years ago
  • kalervo
    How to interface many user forms buttons with multiple functions in VBA?
    2 years ago

Post a comment