idea 不隐藏选项卡
Last month, I showed you my code that hides worksheets, based on the text in their names. Someone asked we could hide sheets based on tab color instead, so that’s how today’s example works.
上个月,我向您展示了根据工作表名称中的文本隐藏工作表的代码。 有人问我们可以根据选项卡的颜色隐藏工作表,所以今天的示例就是这样。
Just select a sheet type from the drop down list, and any sheets with matching tab color are visible. All other sheets are hidden, except the Menu. Then, select "(All)" to see all the sheets again. Watch the video, and get the details, below.
只需从下拉列表中选择一种图纸类型,任何具有匹配选项卡颜色的图纸都将可见。 除菜单外,其他所有工作表均被隐藏。 然后,选择“(全部)”以再次查看所有图纸。 观看视频,并在下面获取详细信息。
创建图纸类型列表 (Create a List of Sheet Types)
The key step is to set up a list of sheet types, and use fill color that matches your sheets’ tab color. You’ll see the sheet type names in the drop down list on the Menu sheet.
关键步骤是设置工作表类型列表,并使用与工作表的标签颜色匹配的填充色。 您会在菜单表的下拉列表中看到表类型名称。
Unfortunately, Excel has very limited formatting in its drop down lists – you can have any font and color you want, as long as it’s Tahoma black. So, use color names in your list, if you’d like to, or function names, or anything else that will help you remember.
不幸的是,Excel的下拉列表格式非常有限-您可以使用任何想要的字体和颜色,只要它是Tahoma黑色即可。 因此,如果需要,请在列表中使用颜色名称,或者使用函数名称,或其他任何可以帮助您记住的内容。
获取设置详细信息 (Get the Setup Details)
There is a video below, that shows how this code works, and explains how to set up your workbook. The video is about 13 minutes long, so I hope you don’t nod off, halfway through it! Maybe you can take a break halfway through it, and to help you jump to the sections of interest, I put the timeline below the video.
下面有一个视频,显示该代码的工作原理,并说明了如何设置您的工作簿。 该视频长约13分钟,所以希望您在播放过程中不要点头! 也许您可以中途休息一下,为了帮助您跳到感兴趣的部分,我将时间轴放在视频下方。
If you prefer written instructions, go to the Show Sheets With Tab Color page on my Contextures website. There are step by step instructions and screen shots, plus all the code.
如果您喜欢书面说明,请转到我的Contextures网站上的“ 用标签颜色显示图纸”页 。 有分步说明和屏幕截图,以及所有代码。
There is also code that lists all the sheets in the workbook, and shows their tab color. That macro is also in the sample file, but isn’t covered in the video.
也有代码列出工作簿中的所有工作表,并显示其选项卡颜色。 该宏也位于示例文件中,但未包含在视频中。
下载样本文件 (Download the Sample File)
To test the macros, you download the sample file, from the Show Sheets With Tab Color page on my Contextures website. The workbook is in xlsm format, and contains macros. Be sure to enable macros when you open the file, if you want to see how the macros work.
若要测试宏,请从我的Contextures网站上的“ 使用选项卡颜色显示图纸”页面下载示例文件。 该工作簿为xlsm格式,并包含宏。 如果要查看宏的工作方式,请确保在打开文件时启用宏。
NOTE: To hide sheets based on their names, download the sample file on this page: Show Specific Excel Sheets (Text)
注意 :要根据其名称隐藏工作表,请在此页面上下载示例文件: 显示特定的Excel工作表(文本)
视频:根据选项卡颜色隐藏工作表 (Video: Hide Sheets Based on Tab Color)
This video shows how the drop down list is set up, and shows how the macros work. There are instructions for setting up this technique in your own workbook. The timeline is below the video.
该视频显示了如何设置下拉列表,并显示了宏的工作方式。 在您自己的工作簿中有设置此技术的说明。 时间线在视频下方。
0:00 Introduction
0:00简介
0:47 How It Works
0:47工作原理
1:21 Named Ranges
1:21命名范围
1:46 Worksheet Macro
1:46工作表宏
3:13 How to Set Up Your Own File
3:13如何设置自己的文件
3:36 Set Up Your Sheets
3:36设置工作表
4:21 Format the Sheet Type List
4:21格式化图纸类型列表
5:50 Name the Sheet Type List
5:50命名图纸类型列表
6:31 Create the Drop Down List
6:31创建下拉列表
7:20 Name the Cell
7:20为细胞命名
7:52 Add the Formulas
7:52添加公式
10:33 Name the Formula Cell
10:33为公式单元格命名
10:59 Add the Code
10:59添加代码
11:56 Test the Code
11:56测试代码
12:43 Get the Sample File
12:43获取示例文件
获取所有Excel新闻 (Get All the Excel News)
For regular Excel news, tips, videos, and special offers, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.
有关常规Excel新闻,提示,视频和特别优惠的信息,请注册Contextures Excel新闻通讯。 您的电子邮件地址将永远不会与任何人共享。
翻译自: https://contexturesblog.com/archives/2016/07/07/hide-sheets-based-on-tab-color/
idea 不隐藏选项卡