Assign Event to a Bunch of Controls :

Hi Guys ,

Many times we have a situation when we have array of controls on which we need same event for every control

So, this can be simplified by using class module where we will have only one event for all the controls.

Example:

Suppose, you have many Command bar Buttons on a user form, and you want to accomplish the same task on every button’s click event or mouse move event.

How would you write events for all the controls?

There are two ways to assign event to a control.

1. Write individual Event for every Control.
2. Write a class Module

Here we go for second option .

You may follow some simple steps to make it done.

However you can create all the buttons in run time, but here I placed all button in design time to make it simple to understand.

First create a user form with many buttons (We are going to work with Command bar button)

2). Insert a Class Module and paste this code

Public WithEvents MyButton As MSForms.CommandButton

Private Sub MyButton_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)   

                   MyForm.Label1.Caption = “Current Value is : ” & MyButton.Caption

    MyButton.SetFocus

End Sub

 

WithEvents Keyword allow us to use Event of declared object (if they have events), So I declared a WithEvents Command Bar Button object in class module

After declare that object with WithEventskeyword, you would be able to select that object from Dropdown are show in below image,  once you select an object from this dropdown VBE provide associated events with that object in Event Dropdown,You can choose any Event as per operational requirement.

3). In form Code Module , paste this code .

Dim ObjButton() As Class1

Private Sub UserForm_Initialize()

    Dim ObjCtrl As Control

    Dim lngCount As Long 

    lngCount = 1

    ReDim ObjButton(1 To 12)

    For Each ObjCtrl In MyForm.Controls

        If TypeName(ObjCtrl) = “CommandButton” Then

            Set ObjButton(lngCount) = New Class1

            Set ObjButton(lngCount).MyButton = ObjCtrl

            lngCount = lngCount + 1

        End If

    Next ObjCtrl

 End Sub

What this code actually doing?

It’s an event of user form which will trigger on initialisation of user form, so we have declared a referenced array of Class1 in first line.

In main code of event, all buttons on user form is being assigned to a new object of that Class1, after execution of this Event all button will have the same functionality

You will have output like this :

In this example we had only one event (MouseMove), we can assign multiple events to every Command bar doing this way ,

Conclusion :

By using class module we don’t need to write Event for every Control individually , it saves lot of time  J

Thanks for Reading

Rajan verma