Excel VBA脚本：清除某一列中的所有复选框

Question

我想清除特定单元格范围内或按列的所有活动X复选框。这似乎很简单，因为我能够使用以下代码清除整个工作表中的所有复选框。但我不知道如何修改它，以仅适用于单个列或单元格范围。

Sub ClearAllCheckboxes()
  Dim Answer As String
  Dim MyNote As String
  Dim Obj As Object

  MyNote = "This will Clear all CheckBoxes Proceed? "
  Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "???")

  If Answer = vbNo Then
    Exit Sub
  Else
    On Error Resume Next
    For Each Obj In ActiveSheet.OLEObjects
         Obj.Object.Value = False
    Next Obj
  End If
End Sub

- Steve Hobbs

Answer 1

Obj.TopLeftCell.Column

会告诉您复选框位于哪一列。根据这个，您可以决定是否要更改它的值。

- Tim Williams

Answer 2

尝试使用 topleftcell 属性：

Sub ClearAllCheckboxes()
  Dim Answer As String
  Dim MyNote As String
  Dim Obj As Object
dim rg as range

set rg=selection

  MyNote = "This will Clear all CheckBoxes Proceed? "
  Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "???")

  If Answer = vbNo Then
    Exit Sub
  Else
    On Error Resume Next
    For Each Obj In ActiveSheet.OLEObjects
        if not intersect(rg, obj.topleftcell) is nothing then _
         Obj.Object.Value = False
    Next Obj
  End If
End Sub

- nutsch

---

在适应现有程序后，它可以完美地工作。非常感谢 :) - Steve Hobbs

@SteveHobbs 作为新用户，请注意您可以从nutsch那里“接受”这个答案。这将为您（2）和nutsch（15）提供声望点数。 - brettdj