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.
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.
- Write individual Event for every Control.
- 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
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
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 ,
By using class module we don’t need to write Event for every Control individually , it saves lot of time J
Thanks for Reading