我正在尝试获得一个我在excel用户表单上动态创建的按钮,用来运行一个名为transfer
的宏,它是我用Module 1
编写的,位于我的项目的“模块”部分。
下面我已经粘贴了到目前为止我在用户表单中编写的代码,它实际上设法在框架中创建了Transfer to Sheet
按钮(我也是动态创建的),但由于某种原因,当我运行VBA时,我收到一条438 error
消息,上面写着Object doesn't support this property or method
。
有人能告诉我如何解决这个问题吗?
代码如下:
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"
发布于 2012-04-19 17:13:25
下面是一个例子。请根据您的需要进行修改:)
此示例将创建一个命令按钮并为其分配代码,以便当按下该按钮时,它将显示"Hello World“。
将此代码粘贴到命令按钮的单击事件中,这将动态创建一个新的命令按钮并为其分配代码。
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
并将此代码粘贴到类模块中
Option Explicit
Public WithEvents CmdEvents As MSForms.CommandButton
Private Sub CmdEvents_Click()
MsgBox "Hello Word"
End Sub
快照
发布于 2012-04-19 19:14:35
您需要以编程方式将代码添加到UserForm中。我使用了这个vbax article中的代码作为参考
代码如下:
私有子CommandButton1_Click()调用转接端子
正常模块中的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
https://stackoverflow.com/questions/10224511
复制相似问题