excel - 创建一个宏，该宏在定义的变量范围内选择/取消选择所有复选框

Question

该宏旨在查找主复选框 (application.caller) 的位置，然后选择该列中最多 14 行以下的所有复选框。我的工作表在其他地方有各种复选框，当前宏检查工作表中的所有复选框。我究竟做错了什么？

Sub SelectAll_Click()
'Select / Clear All macro
Dim xCheckBox As CheckBox, n As Variant, rng As Range, loc As Range, loc1 As Range
'Application.Caller.Name
n = ActiveSheet.CheckBoxes(Application.Caller).Name

With ActiveSheet

Set loc = ActiveSheet.CheckBoxes(Application.Caller).TopLeftCell
'Set loc1 = loc.Address
'MsgBox loc1
loc.Select
Set rng = Range(loc.Address, ActiveCell.Offset(13, 0))
MsgBox (rng.Address)
End With




For Each xCheckBox In Application.ActiveSheet.CheckBoxes
'MsgBox (xCheckBox.TopLeftCell.Address)
'With xCheckBox.Select
   If Not Intersect(loc, rng) Is Nothing Then

        If xCheckBox.Name <> Application.ActiveSheet.CheckBoxes(n).Name Then
       ' If Not Intersect(rngShp, rng) Is Nothing Then

        xCheckBox.Value = Application.ActiveSheet.CheckBoxes(n).Value
        End If

    End If
    'End With

    Next
    End Sub

标签： excelvbacheckbox

Answer 1

对于不是您单击的复选框的所有复选框，以下情况都是正确的。

If xCheckBox.Name <> Application.ActiveSheet.CheckBoxes(n).Name Then
    xCheckBox.Value = Application.ActiveSheet.CheckBoxes(n).Value
End If

您需要另外检查是否xCheckBox.TopLeftCell与您想要的范围相交rng。因此，仅适用TopLeftCell于范围内的所有其他复选框：

If xCheckBox.Name <> Application.ActiveSheet.CheckBoxes(n).Name And Not Intersect(xCheckBox.TopLeftCell, rng) Is Nothing Then
    xCheckBox.Value = Application.ActiveSheet.CheckBoxes(n).Value
End If

---

最后，您的代码可能如下所示：

Option Explicit

Public Sub SelectAll_Click()
    Dim ws As Worksheet
    Set ws = ActiveSheet

    Dim MasterChkBox As CheckBox
    Set MasterChkBox = ws.CheckBoxes(Application.Caller)


    Dim MasterLocation As Range
    Set MasterLocation = ws.CheckBoxes(Application.Caller).TopLeftCell

    Dim Rng As Range
    Set Rng = MasterLocation.Resize(RowSize:=14)

    'MsgBox (Rng.Address)

    Dim xCheckBox As CheckBox
    For Each xCheckBox In ws.CheckBoxes
        If xCheckBox.Name <> MasterChkBox.Name And _
           Not Intersect(xCheckBox.TopLeftCell, Rng) Is Nothing Then

            xCheckBox.Value = MasterChkBox.Value

        End If
    Next xCheckBox
End Sub

^{图 1：如果您选中主框，它只会选择主框下方的 13 个框。}