vba 用于选择和取消选择电子表格中所有其他复选框的复选框
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15301445/
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
Check box to select and deselect all other check boxes in spreadsheet
提问by Jamie Walker
I have a spreadsheet that pulls line items in with check boxes for each line item. I would like to put a check box at the top that will select and deselect all the other check boxes in the spreadsheet when it is selected/deselected. Following is the code I have so far which will select all the check boxes if my "Check Box 1" is selected but it won't deselect them if it is deselected. What do I need to add to make the boxes also deselect if "Check Box 1" is deselected. Thanks for any help.
我有一个电子表格，其中包含每个行项目的复选框。I would like to put a check box at the top that will select and deselect all the other check boxes in the spreadsheet when it is selected/deselected. 以下是我到目前为止的代码，如果我选择了“复选框 1”，它将选择所有复选框，但如果取消选择，则不会取消选择它们。如果取消选择“复选框 1”，我需要添加什么才能使这些框也取消选择。谢谢你的帮助。
Sub SelectAllCheckBox()
Dim CB As CheckBox
If ActiveSheet.CheckBoxes("Check Box 1").Value Then
For Each CB In ActiveSheet.CheckBoxes
If CB.Name <> ActiveSheet.CheckBoxes("Check Box 1").Name Then
CB.Value = True
End If
Next CB
End If
End Sub
I am also running into another problem. I have a macro to clear the sheet so different macros can run. This macro has code in it that deletes all check boxes. How would I word the code to make it not delete "Check Box 1". This is the code I have.
我也遇到了另一个问题。我有一个宏来清除工作表，以便可以运行不同的宏。此宏中包含删除所有复选框的代码。我将如何编写代码以使其不删除“复选框 1”。这是我的代码。
Sheets("Quote Sheet").Select
Range("D3:D7").Select
Selection.ClearContents
Rows("11:1000").Select
Selection.Delete Shift:=xlUp
ActiveSheet.CheckBoxes.Delete
Selection.FormatConditions.Delete
I tried the following but it didn't work.
我尝试了以下但没有奏效。
Sheets("Quote Sheet").Select
Range("D3:D7").Select
Selection.ClearContents
Rows("11:1000").Select
Selection.Delete Shift:=xlUp
If CB.Name <> ActiveSheet.CheckBoxes("Check Box 1").Name Then
ActiveSheet.CheckBoxes.Delete
Selection.FormatConditions.Delete
回答by Tim Williams
Sub SelectAllCheckBox()
Dim CB As CheckBox
For Each CB In ActiveSheet.CheckBoxes
If CB.Name <> ActiveSheet.CheckBoxes("Check Box 1").Name Then
CB.Value = ActiveSheet.CheckBoxes("Check Box 1").Value
End If
Next CB
End Sub
Second part:
第二部分：
Dim CB as CheckBox, n as long, x as long
n = ActiveSheet.CheckBoxes.Count
For x = n to 1 Step -1
Set CB = ActiveSheet.CheckBoxes(x)
If CB.Name <> "Check Box 1" Then CB.Delete
Next x
