如何选中/取消选中电子表格中的所有其他复选框，并隐藏/取消隐藏某些列？

Question

工作表上有6个复选框。

第一部分：

每个复选框(第一个除外)用于隐藏/取消隐藏列。

第二部分：

第一个复选框选中/取消检查其他复选框，当选中它时，它从A到E取消对列的隐藏，如果不选中，则隐藏它们。

总括而言：

checkbox-1: check/uncheck other 5 checkboxes and unhide/hide columns A-E
checkbox-2: hide/unhide column A
checkbox-3: hide/unhide column B
checkbox-4: hide/unhide column C
checkbox-5: hide/unhide column D
checkbox-6: hide/unhide column E

当单击复选框2-6时，我尝试使用下面的代码隐藏/取消隐藏列。啊，真灵。但是我该怎么做第二部分呢？

Sub CheckBox_Click()

    Dim vis As Boolean, ac As String, col As String

    ac = Application.Caller

    With ActiveSheet

        vis = (.Shapes(ac).ControlFormat.Value = 1)

        Select Case ac
            Case "checkbox-2": col = "A"
            Case "checkbox-3": col = "B"
            ...
        End Select

        If col <> "" Then .Columns(col).Hidden = vis

    End With

End Sub

Answer 1

将框2-6连接到CheckTheBoxes宏，将方框1连接到InvertTheBoxes宏.

InvertTheBoxes循环通过纸张的形状。每个不是“方框1”的形状都将被选中/取消选中，并由CheckTheBoxes宏跟随。如果工作表上有更多的形状，则需要稍微更改代码。但这里有一个未经修改的答案：

Option Explicit

'checkbox-1: check/uncheck other 5 checkboxes and unhide/hide columns A-E
'checkbox-2: hide/unhide column A
'checkbox-3: hide/unhide column B
'checkbox-4: hide/unhide column C
'checkbox-5: hide/unhide column D
'checkbox-6: hide/unhide column E

Dim Vis As Boolean, Col$, Shp As Shape

Sub CheckTheBoxes(Optional ByVal AC$)
    ' If a checkbox runs this code, it will use Application.Caller,
    ' since the AC string would be emtpy.
    ' This way, you can run this macro from another macro (InvertTheBoxes), 
    ' provided you return the shape name (which that macro does).
    If AC = "" Then AC = Application.Caller

    With ActiveSheet
        Vis = (.Shapes(AC).ControlFormat.Value = 1)

        Select Case AC
            Case "checkbox-2": Col = "A"
            Case "checkbox-3": Col = "B"
            Case "checkbox-4": Col = "C"
            Case "checkbox-5": Col = "D"
            Case "checkbox-6": Col = "E"
        End Select

        If Col <> "" Then .Columns(Col).Hidden = Vis

    End With
End Sub

Sub InvertTheBoxes()
Dim AC2$
    AC2 = Application.Caller
    With ActiveSheet
        For Each Shp In .Shapes
            If Shp.Name <> AC2 Then
                ' Change the checkbox status (from checked to unchecked and vice versa)
                If Shp.ControlFormat.Value = 1 Then
                    Shp.ControlFormat.Value = -4146 ' Unchecked
                Else
                    Shp.ControlFormat.Value = 1 ' Checked
                End If
                CheckTheBoxes Shp.Name ' Call the hide/show procedure
            End If
        Next Shp
    End With
End Sub

如果要循环使用特定名称的形状，请尝试以下InvertTheBoxes2宏：

Sub InvertTheBoxes2()
Dim AC2$
    AC2 = Application.Caller
    With ActiveSheet
        For Each Shp In .Shapes
            ' Will only loop through shapes starting with "checkbox-"
            If Left(Shp.Name, 9) = "checkbox-" And Shp.Name <> AC2 Then
                ' Change the checkbox status (from checked to unchecked and vice versa)
                If Shp.ControlFormat.Value = 1 Then
                    Shp.ControlFormat.Value = -4146 ' Unchecked
                Else
                    Shp.ControlFormat.Value = 1 ' Checked
                End If
                CheckTheBoxes Shp.Name ' Call the hide/show procedure
            End If
        Next Shp
    End With
End Sub

只需确保您的其他复选框有不同的名称，即"cbo-1“、"cbo-2”等，或者尝试更彻底地命名它们。