Create a series of buttons by clicking the previous in Excel VBA

Question

I'm practicing a class module in Excel VBA.

I have a blank userform inserted manually. From now, no controls will be added manually.

As soon as you initialize the userform, it should appear with a single CommandButton on the Top-Left corner of the form.

Now, Click on the button and you will be served another CommandButton right-below the first button.
Clicking on the newly created button should create another button below the previous button. The process should keep going as long as you keep clicking on the newly created CommandButtons.

Problem
I create the first button on the userform when it is initialized and also create a new button below.
After that nothing happens when clicking on the second/newly created button.

Userform Code

Dim A As New Class2       ' Create an object of the Class (where we declared the events).

Private Sub UserForm_Initialize()

    ' Create and add the button control.
    Dim btEx As MSForms.CommandButton
    Set btEx = UserForm1.Controls.Add("Forms.CommandButton.1")
    
    With btEx
        .Top = 12
        .Left = 12
        .Width = 72
        .Height = 36
        .Caption = "Click Me"
    End With
    
    Set A.btEvents = btEx
    
End Sub

Class Module

Public WithEvents btEvents As MSForms.CommandButton

Private Sub btEvents_click()
        
    ' Create and add the button control.
    Dim btEx As MSForms.CommandButton
    Set btEx = UserForm1.Controls.Add("Forms.CommandButton.1")
    
    With btEx
        .Top = 30
        .Left = 30
        .Width = 72
        .Height = 36
        .Caption = "Click Me"
    End With
        
End Sub

I'm not able to assign the event to the dynamically created CommandButton from the class module.

Am I missing something or is it not possible in the platform?

You need to have a collection to hold all the classes you'll need 1 for each button, i'd have this update after you've set .`btEvents` Something like `Dim a() as Class2` You could also use this array's size to help with your spacing in the class. — Nathan_Sav, Mar 19 '20 at 20:00
@Nathan_Sav Thank you so much for your comment. I have already read a lot of articles and solutions on this and similarly, I did understand your suggestion conceptually but all I'm looking here is, how the syntactically it will be presented! Please. — Kamal Bharakhda, Mar 19 '20 at 20:10
@Nathan_Sav: Thank you so much, Sir. For your statement in the comment, " have a collection to hold all the classes you'll need 1 for each button". Which lead me to the refining my own solution. thank you again. — Kamal Bharakhda, Mar 19 '20 at 21:13

Brian M Stafford · Accepted Answer · 2021-06-22T00:38:19.143

Implementing a collection of classes, where each class contains a button, is the key to making this work. Drawbacks to this common approach are that the class holds a reference to the UserForm, and the events are handled in the class itself. Ideally, we would like to get all the button code back into the UserForm including the handling of events.

How can this be done since Collections cannot be declared WithEvents? This can be accomplished by a global notification mechanism:

UserForm

Option Explicit

Private WithEvents MyNotifier As Notifier
Private MyControls As Collection
    
Private Sub UserForm_Initialize()
   Set MyNotifier = GetNotifier()
   Set MyControls = New Collection
   AddButton
End Sub

Private Sub MyNotifier_Click()
   AddButton
End Sub

Private Sub AddButton()
   Dim c As MSForms.CommandButton
   Set c = UserForm1.Controls.Add("Forms.CommandButton.1")
   c.Width = 72
   c.Height = 36
   c.Top = UserForm1.Controls.Count * c.Height
   c.Left = 12
   c.Caption = "Click Me"

   Dim mc As MyControl
   Set mc = New MyControl
   mc.Add c
   MyControls.Add mc
End Sub

There are 3 support modules with this architecture. The first is a module that holds a global notifier. We create and access this notifier through the GetNotifier method. This method guarantees there is one and only one instance.

Module

Option Explicit

Private m_Notifier As Notifier

Public Function GetNotifier() As Notifier
   If m_Notifier Is Nothing Then Set m_Notifier = New Notifier
   
   Set GetNotifier = m_Notifier
End Function

The second is a class that defines the notifier. This singleton class is an event coordinator. It allows redirection of events, in this case from the MyControl class back to the UserControl.

Notifier Class

Option Explicit

Public Event Click()

Public Function Click()
   RaiseEvent Click
End Function

The third is a class that holds the button. The response to the button click event is to call a method of our Notifier which in turn raises an event back to the UserControl:

MyControl Class

Option Explicit

Private MyNotifier As Notifier
Private WithEvents btEx As MSForms.CommandButton

Public Sub Add(ByVal c As MSForms.CommandButton)
   Set MyNotifier = GetNotifier()
   Set btEx = c
End Sub

Private Sub btEx_Click()
   MyNotifier.Click
End Sub

Thank you for this outstanding solution to this! This could be the best approach after eliminating all the drawbacks of the classical approach. If you could write an explanation for the Notifier Class then it will clear all the doubts regarding your approach! Please. — Kamal Bharakhda, Mar 20 '20 at 07:59
@KamalBharakhda I updated my post with a little more explanation. Hopefully this will be helpful in your understanding. — Brian M Stafford, Mar 20 '20 at 13:21
Dear Sir, thank you so much for your efforts on this solution. I have selected your answer as most helpful amongst all equally correct answers. I think your approach deserves the best approach! Thank you again. If you asked me, I have never seen anything like your approach in any article I have read till. Will you please give me some articles or tutorials where I can learn more deeply about Class Programming for VBA? Because I didn't know about RaiseEvent method to assign events to the controls. I want to learn more about it. Please. — Kamal Bharakhda, Mar 20 '20 at 14:48
@KamalBharakhda I don't have specific resources to recommend. But remember, Google is your friend! This is the way I pick up techniques and knowledge, and likely how I picked up this technique many years ago. I pulled the sample code from an app I've had in production for a long time, simplifying the answer to illustrate the architecture. I'm glad you found it useful. — Brian M Stafford, Mar 20 '20 at 15:18

score 1 · Answer 2 · answered Mar 19 '20 at 21:01

Can you try this. A bit rushed, so wouldn't be surprised if something goes wrong, but will come back tomorrow if so.

Behind the form

Private col As Collection

Private Sub UserForm_AddControl(ByVal Control As MSForms.Control)

Dim cl As Class2
Dim ctl As MSForms.CommandButton

Set col = New Collection

For Each ctl In Me.Controls
    Set cl = New Class2
    Set cl.btEvents = ctl
    col.Add cl
Next ctl

End Sub

Private Sub UserForm_Initialize()

Dim btEx As MSForms.CommandButton
Set btEx = UserForm1.Controls.Add("Forms.CommandButton.1")

With btEx
    .Top = 12
    .Left = 12
    .Width = 72
    .Height = 36
    .Caption = "Click Me"
End With

End Sub

Class module The only change here is to add some spacing so the buttons don't appear on top of each other.

Public WithEvents btEvents As MSForms.CommandButton

Private Sub btEvents_click()

Dim btEx As MSForms.CommandButton
Set btEx = UserForm1.Controls.Add("Forms.CommandButton.1")

With btEx
    .Top = 30 * UserForm1.Controls.Count
    .Left = 30
    .Width = 72
    .Height = 36
    .Caption = "Click Me"
End With

End Sub

Thank you so much for your solution! Nathan_Sav's comment has already cleared my doubts and then I started refining my code again and I found a similar solution like yours. What I was mistaking was, not putting variables in the global module! but your solution has confirmed me the way! Thank you so much for your efforts. I'm sure I'll test your code more, after your tomorrow edit on your answer I'll make it most helpful answer to the question — Kamal Bharakhda, Mar 19 '20 at 21:11
Think the main issue was the code being in the Initialize event as you only have one button by the end of that stage. — SJR, Mar 24 '20 at 15:46

Create a series of buttons by clicking the previous in Excel VBA

2 Answers2

Linked