如何使用 VBA 将事件添加到在 Excel 中运行时创建的控件中

How to add events to Controls created at runtime in Excel with VBA

提问by Fred FLECHE

I would like to add a Control and an associated event at runtime in Excel using VBA but I don't know how to add the events.

我想在运行时使用 VBA 在 Excel 中添加一个控件和一个关联的事件，但我不知道如何添加这些事件。

I tried the code below and the Button is correctly created in my userform but the associated click event that should display the hello message is not working.

我尝试了下面的代码，并且在我的用户表单中正确创建了 Button，但是应该显示 hello 消息的关联点击事件不起作用。

Any advice/correction would be welcome.

欢迎任何建议/更正。

Dim Butn As CommandButton
Set Butn = UserForm1.Controls.Add("Forms.CommandButton.1")
With Butn
    .Name = "CommandButton1"
    .Caption = "Click me to get the Hello Message"
    .Width = 100
    .Top = 10
End With

With ThisWorkbook.VBProject.VBComponents("UserForm1.CommandButton1").CodeModule
    Line = .CountOfLines
    .InsertLines Line + 1, "Sub CommandButton1_Click()"
    .InsertLines Line + 2, "MsgBox ""Hello!"""
    .InsertLines Line + 3, "End Sub"
End With
UserForm1.Show

回答by Shrey Gupta

The code for adding a button at run time and then to add events is truly as simple as it is difficult to find out..I can say that because I have spent more time on this perplexity and got irritated more than in anything else I ever programmed ..

在运行时添加按钮然后添加事件的代码真的很简单，很难找到......我可以这么说是因为我在这个困惑上花了更多的时间并且比其他任何事情都更烦躁编程..

Create a Userform and put in the following code:

创建一个用户表单并输入以下代码：

Option Explicit


Dim ButArray() As New Class2

Private Sub UserForm_Initialize()
    Dim ctlbut As MSForms.CommandButton

    Dim butTop As Long, i As Long

    '~~> Decide on the .Top for the 1st TextBox
    butTop = 30

    For i = 1 To 10
        Set ctlbut = Me.Controls.Add("Forms.CommandButton.1", "butTest" & i)

        '~~> Define the TextBox .Top and the .Left property here
        ctlbut.Top = butTop: ctlbut.Left = 50
        ctlbut.Caption = Cells(i, 7).Value
        '~~> Increment the .Top for the next TextBox
        butTop = butTop + 20

        ReDim Preserve ButArray(1 To i)
        Set ButArray(i).butEvents = ctlbut
    Next
End Sub

---

---

Now U need to add a Class Module to your Code for the project..Please remember its class module not Module.And put in following simple Code( In my case the class name is Class2)-

现在你需要在你的项目代码中添加一个类模块..请记住它的类模块而不是模块。并输入以下简单的代码（在我的例子中类名是 Class2）-

---

---

Public WithEvents butEvents As MSForms.CommandButton

Private Sub butEvents_click()

    MsgBox "Hi Shrey"

End Sub

---

---

Thats it. Now run it

就是这样。现在运行它

回答by dendarii

Try this:

尝试这个：

Sub AddButtonAndShow()

    Dim Butn As CommandButton
    Dim Line As Long
    Dim objForm As Object

    Set objForm = ThisWorkbook.VBProject.VBComponents("UserForm1")

    Set Butn = objForm.Designer.Controls.Add("Forms.CommandButton.1")
    With Butn
        .Name = "CommandButton1"
        .Caption = "Click me to get the Hello Message"
        .Width = 100
        .Top = 10
    End With

    With objForm.CodeModule
        Line = .CountOfLines
        .InsertLines Line + 1, "Sub CommandButton1_Click()"
        .InsertLines Line + 2, "MsgBox ""Hello!"""
        .InsertLines Line + 3, "End Sub"
    End With

    VBA.UserForms.Add(objForm.Name).Show

End Sub

This permanently modifies UserForm1 (assuming you save your workbook). If you wanted a temporary userform, then add a new userform instead of setting it to UserForm1. You can then delete the form once you're done with it.

这将永久修改 UserForm1（假设您保存了工作簿）。如果你想要一个临时的用户表单，那么添加一个新的用户表单而不是将它设置为 UserForm1。完成后，您可以删除该表单。

Chip Pearsonhas some great info about coding the VBE.

Chip Pearson有一些关于 VBE 编码的重要信息。

回答by Daniel

DaveShaw, thx for this code man!

DaveShaw，感谢这个代码人！

I have used it for a togglebutton array (put a 'thumbnail-size' picture called trainer.jpg in the same folder as the excel file for a togglebutton with a picture in it). In the 'click' event the invoker is also available (by the object name as a string)

我已将其用于切换按钮数组（将名为 trainer.jpg 的“缩略图大小”图片与带有图片的切换按钮的 excel 文件放在同一文件夹中）。在 'click' 事件中，调用者也可用（通过对象名称作为字符串）

In the form:

在形式：

Dim CreateTrainerToggleButtonArray() As New ToggleButtonClass 

Private Sub CreateTrainerToggleButton(top As Integer, id As Integer)

Dim pathToPicture As String
pathToPicture = ThisWorkbook.Path & "\trainer.jpg"
Dim idString As String
idString = "TrainerToggleButton" & id

Dim cCont As MSForms.ToggleButton
Set cCont = Me.Controls.Add _
   ("Forms.ToggleButton.1")

With cCont
   .Name = idString
   .Width = 20
   .Height = 20
   .Left = 6
   .top = top
   .picture = LoadPicture(pathToPicture)
   End With

   ReDim Preserve CreateTrainerToggleButtonArray(1 To id)
   Set CreateTrainerToggleButtonArray(id).ToggleButtonEvents = cCont
   CreateTrainerToggleButtonArray(id).ObjectName = idString

   End Sub

and a class "ToggleButtonClass"

和一个类“ToggleButtonClass”

  Public WithEvents ToggleButtonEvents As MSForms.ToggleButton
  Public ObjectName As String


  Private Sub ToggleButtonEvents_click()
  MsgBox "DaveShaw is the man... <3 from your friend: " & ObjectName
  End Sub

Now just simple call from UserForm_Initialize

现在只是来自 UserForm_Initialize 的简单调用

 Private Sub UserForm_Initialize()
   Dim index As Integer
   For index = 1 To 10
     Call CreateTrainerToggleButton(100 + (25 * index), index)
   Next index
 End Sub

回答by Michael Nathan Bain

This was my solution to add a commandbutton and code without using classes It adds a reference to allow access to vbide Adds the button

这是我在不使用类的情况下添加命令按钮和代码的解决方案 它添加了一个引用以允许访问 vbide 添加按钮

Then writes a function to handle the click event in the worksheet

然后写一个函数来处理工作表中的点击事件

Sub AddButton()
Call addref
Set rng = DestSh.Range("B" & x + 3)
'Set btn = DestSh.Buttons.Add(rng.Left, rng.Top, rng.Width, rng.Height)
Set myButton = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Left:=rng.Left, Top:=rng.Top, Height:=rng.Height * 3, Width:=rng.Width * 3)
DoEvents
With myButton
     '.Placement = XlPlacement.xlFreeFloating
     .Object.Caption = "Export"
     .Name = "BtnExport"

     .Object.PicturePosition = 1
     .Object.Font.Size = 14
   End With
   Stop
   myButton.Object.Picture = LoadPicture("F:\Finalised reports\Templates\Macros\evolution48.bmp")

Call CreateButtonEvent

End Sub

Sub addref()
On Error Resume Next
Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB"
Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB"

End Sub


    Private Sub CreateButtonEvent()
On Error GoTo errtrap

    Dim oXl As Application: Set oXl = Application
    oXl.EnableEvents = False
    oXl.DisplayAlerts = False
    oXl.ScreenUpdating = False
    oXl.VBE.MainWindow.Visible = False

    Dim oWs As Worksheet
    Dim oVBproj As VBIDE.VBProject
    Dim oVBcomp As VBIDE.VBComponent
    Dim oVBmod As VBIDE.CodeModule '
    Dim lLine As Single
    Const QUOTE As String = """"

    Set oWs = Sheets("Contingency")
    Set oVBproj = ThisWorkbook.VBProject
    Set oVBcomp = oVBproj.VBComponents(oWs.CodeName)
    Set oVBmod = oVBcomp.CodeModule

    With oVBmod
        lLine = .CreateEventProc("Click", "BtnExport") + 1
        .InsertLines lLine, "Call CSVFile"
    End With

    oXl.EnableEvents = True
    oXl.DisplayAlerts = True
Exit Sub
errtrap:


End Sub

回答by Saolin

An easy way to do it:

一个简单的方法：

1 - Insert a class module and write this code:

1 - 插入一个类模块并编写以下代码：

Public WithEvents ChkEvents As MSForms.CommandButton
Private Sub ChkEvents_click()
MsgBox ("Click Event")
End Sub

2 - Insert a userform and write this code:

2 - 插入一个用户表单并编写以下代码：

Dim Chk As New Clase1
Private Sub UserForm_Initialize()
Dim NewCheck As MSForms.CommandButton
Set NewCheck = Me.Controls.Add("Forms.CommandButton.1")
NewCheck.Caption = "Prueba"
Set Chk.ChkEvents = NewCheck
End Sub

Now show the form and click the button

现在显示表单并单击按钮

回答by KevenDenen

I think the code needs to be added to the Userform, not to the button itself.

我认为代码需要添加到用户窗体，而不是按钮本身。

So something like

所以像

With UserForm1.CodeModule
  'Insert code here
End With

In place of your With ThisWorkbook

代替你的 With ThisWorkbook