在Excel VBA中引用自身的复选框 [英] A checkbox that refers to itself in Excel VBA

问题描述

我有一个Excel电子表格，该表格将单元格的值设置为组中复选框的数量.我想为每个看起来像这样的宏分配一个

I have an excel spreadsheet that sets a cell's value to the number of checked boxes in a group. I would like to assign a macro to each that looks like this:

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，所有框默认为未选中.这样，勾选一个框会增加计数，而取消勾选则会将其击倒一个，并且它永远不会低于零或高于框数.

The cell defaults to 0 and all the boxes default to unchecked. That way, ticking a box ups the count, and unticking it knocks it down one and it can never go below zero or higher than the number of boxes.

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

I realize that I should also make it so that the macro triggers when a checkbox's state is changed not only when it's clicked, but I want to make sure this is possible first.

有没有一种方法可以像这样引用自己?

Is there a way to have a checkbox just reference itself like that?

推荐答案

这取决于您是否绑定ActiveX控件或窗体控件.任何一种都可以工作，并且任何一条路径都可能指导如何清楚地实现它.

It really depends if you're tied into ActiveX controls or Form Controls. Either can work, and either path likely directs how to clearly implement it.

使用ActiveX控件(复选框):

您有两个选项可以为ActiveX控件编写点击处理程序".首先是对每个控件的公共子代码进行硬编码:

You have two options to code your "click handlers" for ActiveX controls. The first is hard-coding a public sub for each control:

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

代码:

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

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

Then if you have ten checkboxes, you'll have ten CheckBox(x)_Click subs, each specifically tied to a single ActiveX control. Each of these click handlers can increment or decrement your counter in stored in a worksheet cell (or in a module private variable).

第二个选项是创建一个可以为任意数量的CheckBox实例化的类模块.

The second option is to create a class module that you can instantiate for any number of CheckBoxes.

类模块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

常规代码模块中的代码:

code in a regular code module:

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

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

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

While there are several ways to catch the click event for a Form checkbox, the simplest is to connect ALL checkboxes in a group to a single macro:

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

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

Either solution can be adapted in many ways, depending on your needs and how you'd like to track your checkboxes.

这篇关于在Excel VBA中引用自身的复选框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！