多个复选框单击事件 [英] multiple checkbox click events

问题描述

我有代码检查单元格中的每个复选框是否都已选中，如果是，则会相应地更改单元格的背景色.

I have code that checks if every checkbox in a cell has been checked and if so changes the cells background color accordingly.

我有一个工作表，上面有900个ActiveX复选框，每个单元格有18个复选框，并且可能会添加更多复选框，我不想编写900加上"Private Sub CheckBox#_Click()"事件.有没有办法为所有事件编写一次事件?

I have a sheet with 900 ActiveX check boxes on, with 18 check boxes per cell, and more may be added, i don't want to have to write 900 plus 'Private Sub CheckBox#_Click()' events. Is there a way to write once event for all of them?

推荐答案

使用 VBA 类模块，可以使成员声明为 WithEvents .因此，可以为可能具有事件的多个对象建立一个事件处理程序".

Using VBA Class modules it is possible having members declared WithEvents. So one "event handler" can be established for multiple objects which can have events.

创建一个新的类模块并命名为 CBEvents .确实有以下代码:

Do creating a new class module and do naming it CBEvents. There do having the following code:

Option Explicit

Private WithEvents oCB As MSForms.CheckBox

Public Property Set CB(obj As MSForms.CheckBox)
 Set oCB = obj
End Property

Private Sub oCB_Click()
 MsgBox oCB.Caption & " was clicked."
End Sub

注意:必须添加对 Microsoft Forms 2.0对象库的引用.如果您要插入 UserForm .

Note: Reference to Microsoft Forms 2.0 Object Library must be added. Will be done automatically if you are inserting a UserForm.

在默认代码模块中，请确保具有以下代码:

In a default code module do having the following code:

Option Explicit

Public aCBEvents() As CBEvents

并且在带有复选框的工作表的代码模块中，确实包含以下代码:

And in the code module of the sheet with the checkboxes in it do having the following code:

Option Explicit

Private Sub Worksheet_Activate()
 Dim oCBEvents As CBEvents
 Dim oleO As OLEObject
 Dim i As Long
 i = 0
 For Each oleO In Me.OLEObjects
  If TypeName(oleO.Object) = "CheckBox" Then
   Set oCBEvents = New CBEvents
   Set oCBEvents.CB = oleO.Object
   ReDim Preserve aCBEvents(i)
   Set aCBEvents(i) = oCBEvents
   i = i + 1
 End If
 Next
End Sub

现在，每次激活工作表时，都会运行 Private Sub Worksheet_Activate()，并为每个复选框启动一个 CBEvents 类的对象，并将其放入数组 aCBEvents .然后，单击事件将从类 CBEvents 的 Private Sub oCB_Click()处理.

Now every time the sheet will be activated the Private Sub Worksheet_Activate() runs and initiates one object of class CBEvents for each checkbox and puts this in the array aCBEvents. And after that, the click event will be handled from the Private Sub oCB_Click() of the class CBEvents.

注意:重新打开工作簿后，必须至少激活工作表一次.因此，如果工作表不是第一张纸，那将是一件好事.否则，您至少需要停用并激活它一次.

Note: After reopening the workbook, the sheet must be activated at least one times. So it would be good, if the sheet would not be the first one. Else you need deactivating and activating it at least one times.

这篇关于多个复选框单击事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！