有没有办法在 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