vba 为动态创建的按钮分配代码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10224511/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Assign code to a button created dynamically
提问by Tony Catton
I'm trying to get a button I've created dynamically on an excel userform form to run a macro called transferwhich I've written in Module 1of the "Modules" section of my project.
我正在尝试获取我在 excel 用户表单上动态创建的按钮，以运行transfer我在Module 1项目的“模块”部分编写的宏。
Below I've pasted the code I've written so far in the userform which actually manages to create the Transfer to Sheetbutton in the frame (which I've also created dynamically) but for some reason, when I run VBA I get a 438 errormessage saying that Object doesn't support this property or method.
下面我粘贴了到目前为止我在用户表单中编写的代码，该代码实际上设法Transfer to Sheet在框架中创建按钮（我也是动态创建的）但是出于某种原因，当我运行 VBA 时，我收到一条438 error消息说Object doesn't support this property or method.
Can anybody tell me how I can resolve this?
谁能告诉我如何解决这个问题？
Here's the code:
这是代码：
Dim framecontrol1 As Control
Set workitemframe = Controls.Add("Forms.Frame.1")
With workitemframe
.Width = 400
.Height = 400
.Top = 160
.Left = 2
.ZOrder (1)
.Visible = True
End With
workitemframe.Caption = "Test"
Set framecontrol1 = workitemframe.Controls.Add("Forms.commandbutton.1")
With framecontrol1
.Width = 100
.Top = 70
.Left = 10
.ZOrder (1)
.Visible = True
.Caption = "Transfer to Sheet"
End With
framecontrol1.OnAction = "transfer"
回答by Siddharth Rout
Here is an example. Please amend it to suit your needs :)
这是一个例子。请修改它以满足您的需求:)
This example will create a command button and assign code to it so that when it is pressed, it will display "Hello World".
此示例将创建一个命令按钮并为其分配代码，以便在按下时显示“Hello World”。
Paste this code in the click event of a command button which will create a new command button dynamically and assign code to it.
将此代码粘贴到命令按钮的单击事件中，这将动态创建一个新的命令按钮并为其分配代码。
Option Explicit
Dim cmdArray() As New Class1
Private Sub CommandButton1_Click()
Dim ctl_Command As Control
Dim i As Long
i = 1
Set ctl_Command = Me.Controls.Add("Forms.CommandButton.1", "CmdXYZ" & i, False)
With ctl_Command
.Left = 100
.Top = 100
.Width = 255
.Caption = "Click Me " & CStr(i)
.Visible = True
End With
ReDim Preserve cmdArray(1 To i)
Set cmdArray(i).CmdEvents = ctl_Command
Set ctl_Command = Nothing
End Sub
and paste this code in a class module
并将此代码粘贴到类模块中
Option Explicit
Public WithEvents CmdEvents As MSForms.CommandButton
Private Sub CmdEvents_Click()
MsgBox "Hello Word"
End Sub
SNAPSHOT
快照
回答by brettdj
You need to add the code to the UserForm programatically. I used my code from this vbax articleas the reference
您需要以编程方式将代码添加到用户窗体。我使用这篇vbax 文章中的代码作为参考
The code below:
下面的代码：
- Runs from a normal module
- Adds the button to a UserForm called UserForm1
Adds this code to the Userform for a Click Event
Private Sub CommandButton1_Click() Call Transfer End Sub
- 从普通模块运行
- 将按钮添加到名为UserForm1的用户窗体
将此代码添加到单击事件的用户窗体
Private Sub CommandButton1_Click() Call Transfer End Sub
VBA from normal module
来自普通模块的 VBA
Sub AddToForm()
Dim UF As Object
Dim frameCOntrol1 As Object
Set UF = ActiveWorkbook.VBProject.VBComponents("UserForm1")
Set frameCOntrol1 = UF.designer.Controls.Add("Forms.CommandButton.1")
With frameCOntrol1
.Width = 100
.Top = 70
.Left = 10
.ZOrder (1)
.Visible = True
.Caption = "Transfer to Sheet"
End With
With UF.CodeModule
.InsertLines 2, _
"Private Sub " & frameCOntrol1.Name & "_Click()" & Chr(13) & _
"Call Transfer" & Chr(13) & _
"End Sub"
End With
End Sub
