如何在Excel中使用VBA在运行时创建的控件添加事件 [英] How to add events to Controls created at runtime in Excel with VBA
问题描述
我想在运行时在Excel中使用VBA添加一个控件和一个关联的事件,但是我不知道如何添加事件。
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.
我尝试了下面的代码,并且在我的用户形式中正确创建了Button,但是应该显示hello消息的关联点击事件不起作用。
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.
欢迎任何建议/更正。
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
推荐答案
在运行时添加按钮然后添加事件的代码是真的很简单,因为很难找出来。我可以说,因为我花了更多的时间在这个困惑,并比我曾经编程的任何其他东西更烦恼。
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 ..
创建一个Userform并输入以下代码:
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
现在U需要添加一个类模块到你的代码的项目。请记住它的类模块不是模块。并放入以下简单的代码(在我的情况下,类名称是Class2) -
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)-
Public WithEvents butEvents As MSForms.CommandButton
Private Sub butEvents_click()
MsgBox "Hi Shrey"
End Sub
这是它。现在运行它
Thats it. Now run it
这篇关于如何在Excel中使用VBA在运行时创建的控件添加事件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!