vba 通过 CommandButton 取消选中整个工作簿中的所有复选框
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16818207/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Uncheck all checkboxes across entire workbook via CommandButton
提问by jcv
I would like to have a code that unchecks all checkboxes named "CheckBox1" for all sheets across the workbook. My current code unfortunately doesn't work, and I'm not sure why - it only works for the active sheet.
我想要一个代码，可以取消选中工作簿中所有工作表的所有名为“CheckBox1”的复选框。不幸的是，我当前的代码不起作用，我不确定为什么 - 它仅适用于活动工作表。
Private Sub CommandButton1_Click()
Dim Sheet As Worksheet
For Each Sheet In ThisWorkbook.Worksheets
Select Case CheckBox1.Value
Case True: CheckBox1.Value = False
End Select
Next
End Sub
回答by
This code iterates through all sheets (except sheets named Sheet100and OtherSheet) and unchecksall your ActiveXcheckboxes named CheckBox1
此代码遍历所有工作表（名为Sheet100和的工作OtherSheet表除外）并取消ActiveX选中所有名为CheckBox1
Sub uncheck_boxes()
Dim ws As Worksheet
Dim xbox As OLEObject
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Sheet100" And ws.Name <> "OtherSheet" Then
For Each xbox In ws.OLEObjects
ws.OLEObjects("CheckBox1").Object.Value = False
Next
End If
Next
End Sub
To uncheck all ActiveXcheckboxes in all sheets disregarding the names used
取消ActiveX选中所有工作表中的所有复选框而不考虑使用的名称
Sub uncheck_all_ActiveX_checkboxes()
Dim ws As Worksheet
Dim xbox As OLEObject
For Each ws In ThisWorkbook.Worksheets
For Each xbox In ws.OLEObjects
ws.OLEObjects(xbox.Name).Object.Value = False
Next
Next
End Sub
To uncheck all Form Control checkboxeson a spreadsheet use
要取消选中Form Control checkboxes电子表格中的所有内容，请使用
Sub uncheck_forms_checkboxes()
Dim ws As Worksheet
Dim xshape As Shape
For Each ws In ThisWorkbook.Worksheets
For Each xshape In ws.Shapes
If xshape.Type = msoFormControl Then
xshape.ControlFormat.Value = False
End If
Next
Next
End Sub
回答by Bathsheba
[edited following comments]
[编辑以下评论]
Try this:
尝试这个：
Sub test()
Dim ws As Excel.Worksheet
Dim s As Object
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Definitions" And ws.Name <> "fx" Then
Set s = Nothing
On Error Resume Next
Set s = ws.OLEObjects("CheckBox1")
On Error GoTo 0
If Not s Is Nothing Then
s.Object.Value = False
End If
End If
Next ws
End Sub
This is a global function (it doesn't belong to a particular sheet), but you can put it inside CommandButton1_Click()if you want.
这是一个全局函数（它不属于特定的工作表），但CommandButton1_Click()如果需要，您可以将其放入其中。
You might not need the error blocking if your sheets (other than Definitions and fx) always contain CheckBox1. Alternatively remove that if statement.
如果您的工作表（定义和 fx 除外）始终包含CheckBox1. 或者删除该 if 语句。
