vba 处理在运行时创建的 OLEObject 命令按钮的事件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14994235/
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
Handling Events for OLEObject CommandButtons created at Runtime
提问by Sunny Sampath
I have struggled with this problem for while now...I want to do something very simple. I want to create multiple commandbuttons at runtime, and then handle events for these commandbuttons with one procedure. So I have built a "withevents" class to handle the automation, but my code is not working. When I run Test(), the CommandButton is created, but when I click on it...there is no messagebox response...I cannot find the error..Please any help would be great!!
我已经在这个问题上挣扎了一段时间......我想做一些非常简单的事情。我想在运行时创建多个命令按钮，然后用一个过程处理这些命令按钮的事件。所以我构建了一个“withevents”类来处理自动化，但我的代码不起作用。当我运行 Test() 时，CommandButton 被创建，但是当我点击它时......没有消息框响应......我找不到错误......请任何帮助都会很棒！
Class cTest
类cTest
Public WithEvents Button As MSForms.CommandButton
Public Sub Button_Click()
s = MsgBox("Hello", vbOKOnly)
End Sub
Module 1
模块一
Public TestCollection As Collection
Sub Test()
Set TestCollection = New Collection
Dim Btn As CommandButton
Dim OLEBtnObj As cTest
Set OLEBtnObj = New cTest
Set Btn = Sheet1.OLEObjects.Add(ClassType:="Forms.CommandButton.1", link:=False,_ DisplayAsIcon:=False, Left:=368.25, Top:=51, Width:=44.25, Height:=24).Object
Set OLEBtnObj.Button = Btn
TestCollection.Add Item:=OLEBtnObj
End Sub
回答by dee
I have one rather impractical solution :-). To test it place the following code in Sheet1 Class Module.
我有一个相当不切实际的解决方案:-)。要测试它，请将以下代码放在Sheet1 Class Module 中。
For each new Sheet1-button a new event handled will be added. This event handler will execute the common event handlerand pass the name of the clicked command button to it.
对于每个新的 Sheet1 按钮，将添加一个处理的新事件。此事件处理程序将执行公共事件处理程序并将单击的命令按钮的名称传递给它。
' Standard Module
Sub test()
' adds three buttons to Sheet1 with click-event handlers
Sheet1.AddButton
ActiveCell.Offset(5, 0).Activate
Sheet1.AddButton
ActiveCell.Offset(5, 0).Activate
Sheet1.AddButton
End Sub
' Sheet1 Class Module
Option Explicit
' Add Microsoft Visual Basic For Applications Extensibility
Public Function AddButton() As MSForms.CommandButton
Dim msFormsCommandButton As MSForms.CommandButton
Set msFormsCommandButton = Me.OLEObjects.Add(ClassType:="Forms.CommandButton.1").Object
CreateEventHandler msFormsCommandButton.Name
Set AddButton = msFormsCommandButton
End Function
Private Sub CommonButton_Click(ByVal buttonName As String)
MsgBox "You clicked button [" & buttonName & "]"
End Sub
Private Sub CreateEventHandler(ByVal buttonName As String)
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim codeText As String
Dim LineNum As Long
Set VBComp = ThisWorkbook.VBProject.VBComponents(Me.CodeName)
Set CodeMod = VBComp.CodeModule
LineNum = CodeMod.CountOfLines + 1
codeText = codeText & "Private Sub " & buttonName & "_Click()" & vbCrLf
codeText = codeText & " Dim buttonName As String" & vbCrLf
codeText = codeText & " buttonName = """ & buttonName & "" & vbCrLf
codeText = codeText & " CommonButton_Click buttonName" & vbCrLf
codeText = codeText & "End Sub"
CodeMod.InsertLines LineNum, codeText
End Sub
