Microsoft Office Tutorials and References
In Depth Information
Handling Multiple UserForm Controls with One Event Handler
Figure 15-21: Many CommandButtons with a single event-handler procedure.
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 .
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()
Dim Msg As String
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
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
Insert a normal VBA module and enter the following code.
This routine simply displays the UserForm.
In the code module for the UserForm, enter the UserForm_Initialize code that