在运行时Excel VBA中为复选框创建事件 [英] Creating events for checkbox at runtime Excel VBA

问题描述

我正在修改此线程中的答案以使其适用于我的代码，但不适用于我。

I am modifying the answer in this thread to make it work for my code but it is not working for me.

如何使用excel vba将事件分配给多个对象？

有两个区别，我使用复选框而不是组合框，但更重要的是，我在运行时创建了复选框。

A couple of differences, I am using a checkbox instead of a combobox but more importantly, I am creating my checkbox at runtime.

这是我的课程：

Public WithEvents checkBox1 As MSForms.checkBox


Private Sub checkBox1_Click()
MsgBox "click"
End Sub'

模块代码：

Dim tbCollection As New Collection

Sub macro1()

Dim cbox As OLEObject
Dim myCheckBox As New JohnClass

Set cbox = ActiveSheet.OLEObjects.Add("Forms.CheckBox.1", Left:=Range("A1"))
Set myCheckBox.checkBox1 = cbox.Object
tbCollection.Add cbox
end sub

我可以看到我引用了新创建的复选框，因为可以更改标题，但单击时没有任何反应。

I can see that I have a reference to the newly created check box because I can change the caption but when I click on it, nothing happens.

推荐答案

您需要将自定义类的实例添加到集合中。更改

You need to add the instance of the custom class to the collection. Change

tbCollection.Add cbox

至

tbCollection.Add myCheckBox

更新：

在以下位置添加OLEObjects似乎存在一些问题运行时并运行其他任何代码。我不知道问题出在哪，但这似乎行得通。

There seems to be some problem with adding OLEObjects at runtime and running any other code. I don't know what the problem is, but this seems to work.

Public tbCollection As Collection

Sub macro1()

    ActiveSheet.OLEObjects.Add ClassType:="Forms.CheckBox.1", Left:=1, Top:=1

    Application.OnTime Now, "AddToClass"

End Sub

Sub AddToClass()

    Dim myCheckBox As JohnClass

    Set tbCollection = New Collection
    Set myCheckBox = New JohnClass

    Set myCheckBox.CheckBox1 = ActiveSheet.OLEObjects(ActiveSheet.OLEObjects.Count).Object
    tbCollection.Add myCheckBox

End Sub

这篇关于在运行时Excel VBA中为复选框创建事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！