处理在运行时创建的OLEObject CommandButtons的事件

问题描述

我现在正在努力解决这个问题，我想做一些非常简单的事情。我想在运行时创建多个命令按钮，然后用一个过程处理这些命令按钮的事件。所以我已经建立了一个withevents类来处理自动化，但我的代码不工作。当我运行Test（），CommandButton被创建，但当我点击它...没有messagebox响应...我找不到错误..请任何帮助将是伟大的!!

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!!

类cTest

Public WithEvents Button As MSForms.CommandButton

Public Sub Button_Click()
s = MsgBox("Hello", vbOKOnly)
End Sub

模块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

推荐答案

我有一个不切实际的解决方案： - ）。要测试它，将以下代码放在 Sheet1类模块中。

I have one rather impractical solution :-). To test it place the following code in Sheet1 Class Module.

对于每个新的Sheet1按钮，将添加一个处理的新事件。此事件处理程序将执行常用事件处理程序，并将点击的命令按钮的名称传递给它。

For each new Sheet1-button a new event handled will be added. This event handler will execute the common event handler and pass the name of the clicked command button to it.

' 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

这篇关于处理在运行时创建的OLEObject CommandButtons的事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！