excel - Excel VBA 中引用自身的复选框

Question

我有一个 Excel 电子表格，它将单元格的值设置为组中复选框的数量。我想为每个宏分配一个如下所示的宏:

Sub clickedBox()
    If thisBox(or however you would do it).Checked = True Then
        Range("D9").Value = Range("D9").Value + 1
    Else
        Range("D9").Value = Range("D9").Value - 1
    End If
End Sub

单元格默认为 0，所有框默认为未选中。这样，勾选一个框就会增加计数，取消勾选它就会减少一，并且它永远不会低于零或高于框的数量。

我意识到我还应该这样做，以便在复选框的状态更改时触发宏，而不仅仅是在单击它时，但我想首先确保这是可能的。

有没有办法让复选框像这样引用自身？

Answer 1

这实际上取决于您是否使用 ActiveX 控件或表单控件。两者都可行，并且任何一条路径都可能指导如何清晰地实现它。

使用 ActiveX 控件(复选框):

您有两种选择来为 ActiveX 控件编写“单击处理程序”。第一个是为每个控件硬编码一个公共(public)子:

Thisworkbook.Sheets("Sheet1") 上的控件:CheckBox1

Excel对象中的代码Sheet1:

Private groupCheckBoxCount As Integer

Private Sub CheckBox1_Click()
    Debug.Print "Control on " & Me.Name & " is now " & Me.CheckBox1.Value
    RegisterCheckedValue Me.CheckBox1.Value
End Sub

Private Sub RegisterCheckedValue(cbVal As Boolean)
    If cbVal = True Then
        Range("CheckBoxCount") = Range("CheckBoxCount") + 1  'choose to store on the sheet
        groupCheckBoxCount = groupCheckBoxCount + 1          'or in a variable
    Else
        Range("CheckBoxCount") = Range("CheckBoxCount") - 1
        groupCheckBoxCount = groupCheckBoxCount - 1
    End If
End Sub

然后，如果您有 10 个复选框，则将有 10 个 CheckBox(x)_Click 子项，每个子项专门绑定(bind)到一个 ActiveX 控件。这些点击处理程序中的每一个都可以增加或减少存储在工作表单元格(或模块私有(private)变量)中的计数器。

第二个选项是创建一个类模块，您可以为任意数量的复选框实例化该模块。

类模块代码MyCheckBoxClass

Dim WithEvents cbControl As MSForms.CheckBox

Private controlName As String

Public Sub cbControl_Click()
    Debug.Print controlName & " is now " & cbControl.Value
    If cbControl.Value = True Then
        Range("CheckBoxCount") = Range("CheckBoxCount") + 1  'choose to store on the sheet
        groupCheckBoxCount = groupCheckBoxCount + 1          'or in a variable
    Else
        Range("CheckBoxCount") = Range("CheckBoxCount") - 1
        groupCheckBoxCount = groupCheckBoxCount - 1
    End If
End Sub

Public Sub Attach(newCB As MSForms.CheckBox, newName As String)
    Set cbControl = newCB
    controlName = newName
End Sub

Private Sub Class_Initialize()
    controlName = ""
End Sub

常规代码模块中的代码:

Public groupClickCount As Integer
Private cbCollection As Collection

Public Sub SetUpControlsOnce()
    Dim thisCB As MyCheckBoxClass
    Dim ctl As OLEObject
    Dim cbControl As MSForms.CheckBox

    If cbCollection Is Nothing Then
        Set cbCollection = New Collection
    End If

    For Each ctl In ThisWorkbook.Sheets("Sheet1").OLEObjects
        If TypeName(ctl.Object) = "CheckBox" Then
            '--- this is an ActiveX CheckBox
            Set thisCB = New MyCheckBoxClass
            thisCB.Attach ctl.Object, ctl.name
            cbCollection.Add thisCB
        End If
    Next ctl
End Sub

使用表单控件(复选框):

虽然有多种方法可以捕获表单复选框的点击事件，但最简单的是将组中的所有复选框连接到单个宏:

Public groupClickCount As Integer

Public Sub cbControl_Click()
    '--- loop through all the controls on the form and filter for
    '    only checkboxes, then count up how many are checked
    Dim ctl As Shape
    Dim checkCount As Integer
    checkCount = 0
    For Each ctl In ActiveSheet.Shapes
        If ctl.Type = msoFormControl Then
            On Error Resume Next
            If ctl.ControlFormat = xlCheckBox Then
                If ctl.ControlFormat.Value = 1 Then
                    checkCount = checkCount + 1
                Else
                    checkCount = checkCount - 1
                End If
            End If
        End If
    Next ctl
    Range("CheckBoxCount") = checkCount 'choose to store on the sheet
    groupClickCount = checkCount        'or in a variable
End Sub

这两种解决方案都可以通过多种方式进行调整，具体取决于您的需求以及您希望如何跟踪复选框。