通过在Excel VBA中单击每个按钮来创建一系列动态创建的按钮 [英] Create a Series of dynamically created button by clicking each of them in Excel VBA

问题描述

请不要将此问题视为重复的问题！这 这个问题显然与早期类似的查询有所不同.

Please don't consider this question as a repeated question! This question is clearly different from earlier similar-looking queries.

最近几天，我正在Excel VBA中练习一个类模块.

Last few days, I'm practicing a class module in Excel VBA.

我向自己介绍了一个非常简单的任务，但结果却像冠军！

I have presented myself a very simple looking task but it turned out like a champ!

我的任务很简单！

挑战

我手动插入了空白用户表单！从现在开始，将不会手动添加任何控件.

I have a BLANK Userform inserted manually! From now, no controls will be added manually.

初始化用户窗体后，它应该在窗体的左上角带有一个单独的CommandButton出现.

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

现在，单击该按钮，您将在该按钮的右下方成为另一个CommandButton的服务器！因此，单击新创建的按钮，将在上一个按钮的正下方再次创建另一个按钮，只要您继续单击新创建的CommandButton，该过程就会继续进行.

Now, Click on the button and you will be server another CommandButton right-below of the button! And so, click on the newly created button and another button will be created again right below of the previous button and the process keep going on as long as you keep clicking on the newly created CommandButtons.

我面临的问题

初始化用户窗体时，我可以在其上创建第一个按钮，也可以在其下方创建一个新按钮.但是之后没有.当我单击第二个/新创建的按钮时，没有任何反应.

I'm able to create the First button on the Userform when it was initialized and also able to create a new button below of it. But Nothing after that. Nothing happens when I'm clicking on the second/newly created button.

这是我的用户表单代码

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

这是课程模块

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

需要您的见解或帮助您理解我的正确做法吗?我无法从类模块将事件分配给动态创建的CommandButton.

Need your insights or help to understand am I going in the right way? I'm not able to assign the event to the dynamically created CommandButton from the class module.

请让我知道，我是否丢失了某些东西，或者甚至在平台中都无法做到?我真的是Excel VBA类模块编程中的新手.

Please let me know, am I missing something or it's not even possible in the platform? I'm really a kind of a newbie in Excel VBA Class Module programming.

先谢谢您.

推荐答案

实现一个类的集合(其中每个类都包含一个按钮)是完成这项工作的关键.通用方法的缺点是该类持有对UserForm的引用，并且事件在类本身中处理.理想情况下，我们希望将所有按钮代码(包括事件处理)重新带回到UserForm中.

Implementing a collection of classes, where each class contains a button, is the key to making this work. Drawbacks to the 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.

由于无法将Collection声明为WithEvents，该怎么办?这可以通过全局通知机制来实现:

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

用户窗体

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

此架构有3个支持模块.第一个是包含全局通知程序的模块.我们通过GetNotifier方法创建并访问此通知程序.这种方法保证只有一个实例.

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.

模块

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

第二个是定义通知程序的类.该单例类是事件协调器.它允许事件重定向，在这种情况下，该事件从MyControl类重定向回UserControl.

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.

通知者类

Option Explicit

Public Event Click()

Public Function Click()
   RaiseEvent Click
End Function

第三个是保存按钮的类.对按钮单击事件的响应是调用通告程序的方法，该方法又将事件引发回UserControl:

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类

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

这篇关于通过在Excel VBA中单击每个按钮来创建一系列动态创建的按钮的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！