Excel VBA将处理程序添加到窗体中的每个复选框 [英] Excel VBA add handler to every checkbox in form

问题描述

我有一个Excel表单，其中包含大量在运行时添加的复选框。我想为更改值时将运行的每个复选框添加一个处理程序。我知道在其他版本的Visual Basic中，我将使用AddHandler，但这在Excel VBA中不起作用。

I have an excel form with a large number of checkboxes that are added at runtime. I would like to add a handler to each one of those checkboxes that will run when the value is changed. I know in other versions of Visual Basic I would use AddHandler, but that doesn't work in Excel VBA.

在下面的示例中，我想到了以下代码：

Following an example, I came up with the following code:

'This is in a class module called CheckboxHandler
Public WithEvents cb As MSForms.CheckBox

Private Sub cb_change()
    MsgBox ("test")
end sub

然后，在我的用户窗体中，我有以下代码：

And, in my userform, I have this code:

With CreateObject("Scripting.Dictionary")
.....'Unrelated code omitted

'Variable Checkboxes
'Add Handler to checkboxes
Dim colCBHandlers As Collection
Set colCBHandlers = New Collection
Dim objHandler As CheckboxHandler

Dim i As Long
Dim chkBox As MSForms.CheckBox
For i = 1 To .count - 1
    Set chkBox = Me.Controls.Add("Forms.Checkbox.1", "Checkbox" & i)
    chkBox.Caption = .Keys()(i)
    chkBox.VALUE = False
    chkBox.Top = (chkBox.Height + 10) * (i - 1) + 55
    chkBox.Left = 725
    Set objHandler = New CheckboxHandler
    Set objHandler.cb = chkBox
    colCBHandlers.Add objHandler
Next i
End With

推荐答案

<$一旦创建复选框的子项退出，c $ c> colCBHandlers 就会超出范围。

您需要将该集合声明为全局集合（在模块级别），这样它在创建和填充后就不会丢失。

You need to declare that collection as a global (at the module level) so it doesn't get lost once it has been created and populated.

这篇关于Excel VBA将处理程序添加到窗体中的每个复选框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！