excel - 如何切换链接到特定范围的复选框？

Question

我有链接到各自单元格的复选框。当我选中 checkbox1 时，我希望选中/取消选中特定范围内的所有复选框。

这是我使用的，但它不起作用。它给了我错误Object variable or With block variable not set。

Sub SelectAll_Click()
    Dim xCheckBox As CheckBox
    Dim rng As Range, cell As Range
    Set rng = Range("B19:B28")

    For Each cell In rng
        If xCheckBox.Name <> Application.ActiveSheet.CheckBoxes("Check Box 1").Name Then
            xCheckBox.Value = Application.ActiveSheet.CheckBoxes("Check Box 1").Value
        End If
    Next cell
End Sub

谢谢

标签： excelvba

Answer 1

要切换checkbox链接到单元格的 a，您只需将单元格值（或公式的返回值）设置为True或False。

使用您的示例，它看起来像：

Sub SelectAll_Click()
    Dim xCheckBox As CheckBox
    Dim rng As Range, cell As Range
    Set rng = Range("B19:B28")

    For Each cell In rng
        cell.value = True
    Next cell
End Sub

---

如果您需要基于复选框本身的逻辑，那么您将改为循环复选框而不是 a 范围。

Private Sub demoLoopingCheckboxes()
    Dim control As OLEObject
    For Each control In ActiveSheet.OLEObjects
        With control
            ' The type of activex control
            ' Use this is a if statement to limit to only "CheckBox"
            Debug.Print TypeName(.Object)
            
            ' The cell Address to the linked cell
            Debug.Print .LinkedCell
            
            ' Can read/write the value to the checkbox itself
            Debug.Print .Object.Value
        End With
    Next control
End Sub