展开或最小化 Excel 选项卡栏上的工作表组

展开或最小化 Excel 选项卡栏上的工作表组

有没有办法在 Excel 2010 中对工作表进行分组,以便并非所有选项卡都立即可见?基本上,这样它们就可以根据需要展开和最小化。

我有几个 Excel 工作簿,里面有很多标签。滚动查找特定工作表有点麻烦。

答案1

这里,我草草写了一些东西,您可以随意修改。它可能会更短,但您明白我的意思了。

插入一个组合框(在主表上,我的名为“控件”),为其分配一个列表,然后将此宏放入 Visual Basic 中。按您喜欢的方式分组,确保始终有一张表可见,并留出一个框来显示所有内容。

Sub DropDown1_Change()

With ActiveSheet.Shapes(Application.Caller)
        Select Case .ControlFormat.ListIndex

        Case 1
            Sheets("Control").Visible = xlSheetVisible
            Sheet2.Visible = xlSheetVisible
            Sheet3.Visible = xlSheetVisible
            Sheet4.Visible = xlSheetHidden
            Sheet5.Visible = xlSheetHidden
            Sheet6.Visible = xlSheetHidden
            Sheet7.Visible = xlSheetHidden
            Sheet8.Visible = xlSheetHidden
            Sheet9.Visible = xlSheetHidden
            Sheet10.Visible = xlSheetHidden
            Sheet11.Visible = xlSheetHidden

        Case 2
            Sheets("Control").Visible = xlSheetVisible
            Sheet2.Visible = xlSheetHidden
            Sheet3.Visible = xlSheetHidden
            Sheet4.Visible = xlSheetVisible
            Sheet5.Visible = xlSheetVisible
            Sheet6.Visible = xlSheetHidden
            Sheet7.Visible = xlSheetHidden
            Sheet8.Visible = xlSheetHidden
            Sheet9.Visible = xlSheetHidden
            Sheet10.Visible = xlSheetHidden
            Sheet11.Visible = xlSheetHidden

        Case 3
            Sheets("Control").Visible = xlSheetVisible
            Sheet2.Visible = xlSheetHidden
            Sheet3.Visible = xlSheetHidden
            Sheet4.Visible = xlSheetHidden
            Sheet5.Visible = xlSheetHidden
            Sheet6.Visible = xlSheetVisible
            Sheet7.Visible = xlSheetVisible
            Sheet8.Visible = xlSheetHidden
            Sheet9.Visible = xlSheetHidden
            Sheet10.Visible = xlSheetHidden
            Sheet11.Visible = xlSheetHidden

        Case 4
            Sheets("Control").Visible = xlSheetVisible
            Sheet2.Visible = xlSheetHidden
            Sheet3.Visible = xlSheetHidden
            Sheet4.Visible = xlSheetHidden
            Sheet5.Visible = xlSheetHidden
            Sheet6.Visible = xlSheetHidden
            Sheet7.Visible = xlSheetHidden
            Sheet8.Visible = xlSheetVisible
            Sheet9.Visible = xlSheetVisible
            Sheet10.Visible = xlSheetHidden
            Sheet11.Visible = xlSheetHidden

        Case 5
            Sheets("Control").Visible = xlSheetVisible
            Sheet2.Visible = xlSheetHidden
            Sheet3.Visible = xlSheetHidden
            Sheet4.Visible = xlSheetHidden
            Sheet5.Visible = xlSheetHidden
            Sheet6.Visible = xlSheetHidden
            Sheet7.Visible = xlSheetHidden
            Sheet8.Visible = xlSheetHidden
            Sheet9.Visible = xlSheetHidden
            Sheet10.Visible = xlSheetVisible
            Sheet11.Visible = xlSheetVisible

        Case 6
        For Each ws In Worksheets
        ws.Visible = xlSheetVisible

        Next
        End Select

    End With
End Sub

相关内容