复选框以选择并取消选择电子表格中的所有其他复选框 [英] Check box to select and deselect all other check boxes in spreadsheet

问题描述

我有一个电子表格，用于在每个订单项的复选框中提取订单项。我想在顶部放置一个复选框，当选择/取消选择时，它将选择并取消选中电子表格中的所有其他复选框。以下是我到目前为止的代码，如果我选择了复选框1，它将选中所有复选框，但如果取消选中，则不会取消选择。如果复选框1被取消选择，我需要添加什么来使框也取消选择。感谢任何帮助。

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.

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

我也遇到另一个问题。我有一个宏来清除表格，以便不同的宏可以运行。此宏包含代码，删除所有复选框。如何将代码写入不会删除复选框1。这是我的代码。

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.

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

推荐答案

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

第二部分：

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

这篇关于复选框以选择并取消选择电子表格中的所有其他复选框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！