在VBA中动态定义复选框的事件 [英] Dynamically define the events of the checkboxes in VBA
问题描述
我已经为UserForm动态定义了复选框。
请参见代码:
I have defined dynamically the checkboxes for my UserForm. See the code:
If rs.EOF = False Then
i = 1
Do Until rs.EOF Or i = 6
With frmOne.Controls("Version" & i)
.Visible = True
.Caption = rs!versNo & "#" & rs!Vers_From
.tag = rs!versNo & "_" & rs!noID & ".pdf"
End With
i = i + 1
rs.MoveNext
Loop
End If
对于这些复选框,我需要一个 Click事件。例如:
Well, for these checkboxes I need a "Click"-Event. For example:
Private Sub Version1_Click()
If FilterOk = True Then
VersNr = Mid(frmOne.Version1.tag, 1, InStr(frmOne.Version1.tag, "_") - 1)
Call funcVersion
Exit Sub
End If
...
End Sub
我该如何使之动态?我不需要一个CommandButton。这意味着,当用户单击Checkbox时,事件处理程序将启动。
How can I make dynamic? I don't need a CommandButton for that. That means, when the user click on the Checkbox then the eventhandler starts.
推荐答案
Rory认为这是重复项是正确的,但是由于我已经写了一些代码段,因此将其发布在这里。我希望这不违反规则。您需要使用将处理事件的启用事件的对象创建一个自定义类。然后,您可以将对复选框的引用分配给这些对象。
Rory is right about this being a duplicate, but since I already wrote a little snippet, I will post it here. I hope that it is not against the rules. You need to create a custom class with an event-enabled object that will handle the events. You can then assign reference to your checkboxes to these objects.
一个简单的演示示例:
创建一个名为 CheckBoxEventHandler
的类模块,并将以下代码放入该类模块中。
Create a class module named CheckBoxEventHandler
and place the following code inside the class module.
' This will store a reference to a checkbox and enable handling its events.
Private WithEvents m_chckBox As MSForms.CheckBox
' Method to assign a reference to a checkbox to your event handler
Public Sub AssignCheckBox(c As MSForms.CheckBox)
Set m_chckBox = c
End Sub
' Private sub to execute something on the event
Private Sub m_chckBox_Click()
MsgBox "Checkbox" + m_chckBox.Caption + "clicked"
End Sub
创建带有某些复选框的用户表单,并将以下代码放入其模块:
Create a userform with some checkboxes and place the following code in its module:
' Define a collection to store your event handlers while the userform is active.
Private eventHandlerCollection As New Collection
Private Sub UserForm_Initialize()
Dim chckBoxEventHandler As CheckBoxEventHandler, c As Control
For Each c In UserForm1.Controls
If TypeName(c) = "CheckBox" Then
'Create event handler instance
Set chckBoxEventHandler = New CheckBoxEventHandler
'Assign it reference to a checkbox
chckBoxEventHandler.AssignCheckBox c
'Store the event handler in the userform's collection,
eventHandlerCollection.Add chckBoxEventHandler
End If
Next
End Sub
在您的情况下实现此功能的方法
(我没有您的确切代码,因此我无法对其进行测试,但我认为它应该给你一个大概的想法。)
(I don't have your exact code so I was not able to test it, but I believe that it should give you the general idea.)
1。创建一个名为 CheckboxEventHandler
Public WithEvents chckBox As MSForms.CheckBox
Private Sub chckBox_Click()
Debug.Print "Checkbox" + chckBox.Caption + "clicked"
' Do your click-handler logic here.
' If you need private variables that are defined elsewhere, you can define the function
' whereever you need it and use the eventhandler only to call it and pass it a reference to the clicked checkbox:
Call somefunction(chckBox)
' Or you could define the function as a public method in frmOne and call it from here like this:
Call frmOne.somefunction(chckBox)
End Sub
2。在 frmOne
用户表单的代码开头处添加以下内容:
2. Add the following at the beggining of the code in your frmOne
userform:
' Define a collection to store event handlers.
Private eventHandlerCollection As New Collection
' Method for adding clickhandlers to checkBoxes dynamically
Public Sub createClickHandler(c As MSForms.CheckBox)
Dim eventHandler As New CheckBoxEventHandler
eventHandler.chckBox = c
Call eventHandlerCollection.Add(eventHandler)
End Sub
3。将事件处理程序附加到复选框
3. Attach the event handlers to the checkboxes
If rs.EOF = False Then
i = 1
Do Until rs.EOF Or i = 6
With frmOne.Controls("Version" & i)
.Visible = True
.Caption = rs!versNo & "#" & rs!Vers_From
.Tag = rs!versNo & "_" & rs!noID & ".pdf"
End With
'register event listener
frmOne.createClickHandler (frmOne.Controls("Version" & i))
i = i + 1
rs.MoveNext
Loop
End If
这篇关于在VBA中动态定义复选框的事件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!