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:

Image

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 :

Image

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

4 Comments Add yours

  1. al swinby says:

    I get syntax error on this line
    MyForm.Label1.Caption = “Current Value is : ” & MyButton.Caption
    what should be the name of the userform
    what should be the names of the command buttons?
    thanks, but not enough explanation
    I have placed buttons and label on a userform

  2. hi ,
    thanks for your comment.
    you just need to change userForm Name as “MyForm” , you dont need to change name of labels and commandbar button .

    Thanks

  3. Tim Jeffryes says:

    It would be advisable to destroy the array when the form unloads to clear memory. Use ERASE. Either that or loop through the array and set each element to nothing.

  4. al swinby says:

    My Userform is called “MyForm” so that is not the reason why it does not work.
    Thanks for reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s