Challenging problem - Generic CommandButton Click Event w/o a seperate Class module !!

[Jaafar Tribak]

Greetings all.

Let me see if I can explain this clearly.

Suppose I have a UserForm with 10 CommandButtons. Each CommandButton has the same code inside its _Click event handler.

Now, because the Click event code for each CommandButton are the same ,we can, as you know, use a small Class module to define a generic _Click event handler for all the CommandButtons and then upon initializing the UserForm we can create 10 instances of the Class Module and assign each CommandButton to a Public variable declared inside the Class module via the Keyword WithEvents .

Example :

1- Code in the Class module ( Class named cmbClass )

Public WithEvents CmbGroup As MSForms.CommandButton
 
Private Sub CmbGroup_Click()
 
    MsgBox CmbGroup.Name
 
End Sub

2- Code in the UserForm module :

Option Base 1
 
Dim cmbClassArray() As cmbClass
 
Private Sub UserForm_Initialize()
 
    Dim cmbClassInstance As cmbClass
    Dim oCtl As Control
    Dim i As Long
 
    For Each oCtl In Me.Controls
 
         If TypeOf oCtl Is CommandButton Then
 
            Set cmbClassInstance = New cmbClass
            Set cmbClassInstance.CmbGroup = oCtl
            i = i + 1
            ReDim Preserve cmbClassArray(i)
            Set cmbClassArray(i) = cmbClassInstance
 
        End If
 
    Next
 
    Set oCtl = Nothing
    Set cmbClassInstance = Nothing
 
End Sub

Now my question is : How can we create a generic control event WITHOUT having to add a seperate Class module hence keeping the entire code encapsulated inside the UserForm module ?

My initial thought was to declare the WithEvents variable inside the UserForm Module (Given the fact that the UserForm module is itself a Class module ) to avoid the need of a separate Class and to keep the entire code within the userform.

So I was thinking to declare the variable WithEvents as an array, something like :

Private WithEvents CmbGroup() As MSForms.CommandButton

to store a reference to each of the 10 CommandButtons but obviously that didn't work.

Another idea i had was to instantiate 10 userforms (1 for each CommandButton) in the UserForm_Initialize event but obviously that would be crazy as that would create an infinite loop !

Any thoughts ?

Regards.


EDIT : Sorry I forgot to mention that i don't want to declare 10 seperate WithEvents variables at the top of the userform module one for each CommandButton which i know will work as that would make the code cumbersome to write ( Imagine having dozens of commandbuttons ! ) I am actually looking for a more elegant solution.

 

[Jaafar Tribak]

^ Any ideas ? ^

 

[Jaafar Tribak]

Up

 

[Norie]

Jaafar

Probably not really relevant, but why are you creating a new instance of the class for each commandbutton?

I'm no expert using classes but I've never seen a reason to do that.

 

[Jaafar Tribak]

Jaafar

Probably not really relevant, but why are you creating a new instance of the class for each commandbutton?

I'm no expert using classes but I've never seen a reason to do that.

Thanks Norie for the interest.

I have to create a new instance for each commandbutton before placing a reference to the new instance in a module level array otherwise the CmbGroup Property will reference the last CommandButton in the loop .

Regards.

 

[Jaafar Tribak]

I am just posting a stable solution (hopefully) to this problem.

Workbook example.

With this code design, one can have an array of controls sharing event routines within the userform module itself - ie : Making the UserForm the Server and the Client at the same time hence no need to add a seperate class module to the project !

The following example shows how all the commandbuttons existing on the UserForm can sink their respective _click events within a single routine located inside the userform.

Option Explicit
 
Public WithEvents CmBtn As CommandButton
Private oFormsArray() As UserForm

Private Sub UserForm_Initialize()

    If VBA.UserForms.Count > 1 Then Exit Sub
    
    'add your initialize code here....
 
End Sub
 
Private Sub UserForm_Terminate()
 
    Dim oForm As UserForm
    
    For Each oForm In VBA.UserForms
    
        Unload oForm
        
    Next
 
End Sub
 
Private Sub UserForm_Activate()
 
    Dim oNewForm As Object
    Dim oCtl As Control
    Dim i As Long

    For Each oCtl In Me.Controls
    
        If TypeOf oCtl Is CommandButton Then
         
          Set oNewForm = VBA.UserForms.Add(Me.Name)
          
          ReDim Preserve oFormsArray(i)
          
          Set oNewForm.CmBtn = oCtl
          
          Set oFormsArray(i) = oNewForm
          
          i = i + 1
         
        End If
    
    Next
 
End Sub
 
Private Sub CmBtn_Click()
    
    MsgBox "You Cliked : " & vbNewLine & _
    vbNewLine & CmBtn.Parent.ActiveControl.Name, _
    vbInformation
 
End Sub