vba 以编程方式向用户窗体添加命令按钮

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/1917908/
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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 11:07:16  来源:igfitidea点击:

Programmatically adding a commandbutton to a userform

excelvbaexcel-vba

提问by asel

In excel vba I have added a commandbutton to userform... like below

在 excel vba 中,我向用户窗体添加了一个命令按钮......如下所示

      Set ctrl = Me.Controls.Add( _
      bstrProgID:="Forms.CommandButton.1", _
      Name:="CommandButton1", Visible:=True)

Now I wanted to know how would I tell it what to do when it is clicked?

现在我想知道当它被点击时我该如何告诉它做什么?

回答by Oorang

This is one of those techniques that vba will letyou do, but you probably shouldn't. For all the same reasons you shouldn't use code that alters your code.

这是 vba允许您使用的技术之一,但您可能不应该这样做。出于同样的原因,您不应该使用更改代码的代码。

That said, here is how to do what you want. First insert a class module and name it DynBtn, then paste this code into it:

也就是说,这是如何做你想做的。首先插入一个类模块并将其命名为 DynBtn,然后将这段代码粘贴到其中:

Private WithEvents mobjBtn As MSForms.CommandButton
Private msOnAction As String
''// This has to be generic or call by name won't be able to find the methods
''// in your form.
Private mobjParent As Object

Public Property Get Object() As MSForms.CommandButton
    Set Object = mobjBtn
End Property

Public Function Load(ByVal parentFormName As Object, ByVal btn As MSForms.CommandButton, ByVal procedure As String) As DynBtn
    Set mobjParent = parentFormName
    Set mobjBtn = btn
    msOnAction = procedure
    Set Load = Me
End Function

Private Sub Class_Terminate()
    Set mobjParent = Nothing
    Set mobjBtn = Nothing
End Sub

Private Sub mobjBtn_Click()
    CallByName mobjParent, msOnAction, VbMethod
End Sub

Now to use this in your form, create a blank user form and paste this code into it:

现在要在您的表单中使用它,请创建一个空白用户表单并将此代码粘贴到其中:

Private Const mcsCmdBtn As String = "Forms.CommandButton.1"
Private mBtn() As DynBtn

Private Sub UserForm_Initialize()
    Dim i As Long
    ReDim mBtn(1) As DynBtn
    For i = 0 To UBound(mBtn)
        Set mBtn(i) = New DynBtn
    Next
    ''// One Liner
    mBtn(0).Load(Me, Me.Controls.Add(mcsCmdBtn, "Btn1", True), "DoSomething").Object.Caption = "Test 1"
    ''// Or using with block.
    With mBtn(1).Load(Me, Me.Controls.Add(mcsCmdBtn, "Btn2", True), "DoSomethingElse").Object
        .Caption = "Test 2"
        .Top = .Height + 10
    End With
End Sub

Public Sub DoSomething()
    MsgBox "It Worked!"
End Sub

Public Sub DoSomethingElse()
    MsgBox "Yay!"
End Sub

Private Sub UserForm_Terminate()
    Erase mBtn
End Sub