olami 自定义上下文_自定义Excel上下文菜单

olami 自定义上下文

When you right-click in Excel, a pop-up menu appears, with a list of commands that you can use. The list changes, depending on where you've clicked, so it's called a "Context Menu".

在Excel中右键单击时，会出现一个弹出菜单，其中包含您可以使用的命令列表。 该列表会根据您单击的位置而变化，因此称为“上下文菜单”。

But, even though those pop-up menus are helpful, they might not have all the commands that you like to use. Or, the commands might be there, but buried a few layers deep in the sub-menus.

但是，即使这些弹出菜单很有用，但它们可能未包含您要使用的所有命令。 或者，命令可能在那里，但在子菜单中深埋了几层。

Maybe you'd like to add a few commands, but there isn't a built-in way to customize those menus, like there is for the Ribbon and Quick Access Toolbar (QAT).

也许您想添加一些命令，但是没有内置的方法可以自定义这些菜单，例如功能区和快速访问工具栏(QAT)。

AlexJ的救援 (AlexJ to the Rescue)

Fortunately, there is good news! Last week, I heard from my friend, and fellow Canadian, AlexJ. He was tired of travelling up to the QAT, where his favourite commands were stored, so he created code that adds new items to the Context Menus, and sent me his sample workbook.

幸运的是，有个好消息！ 上周，我收到了朋友和加拿大同胞AlexJ的来信。 他已经厌倦了前往存储了他最喜欢的命令的QAT的麻烦，因此他创建了将新项目添加到“上下文菜单”的代码，并向我发送了他的示例工作簿。

Thanks, AlexJ, for sharing your sample file, so we can all customize our Context Menus. I'll let AlexJ tell you his story, while I add a few of my favourite commands to that code.

感谢AlexJ，感谢您共享示例文件，因此我们所有人都可以自定义上下文菜单。 我将让AlexJ告诉您他的故事，同时在该代码中添加一些我最喜欢的命令。

它是如何发生的 (How It Happened)

Alex J: A lot of my work lately involves reviewing tables and pivot tables with a team in meetings and MS Lync conferences. When we’re doing this, there is typically a lot of filtering of tables, pivot tables, or ranges to zoom in on groups of data.

Alex J：最近我的很多工作都涉及与团队在会议和MS Lync会议上审查表和数据透视表。 执行此操作时，通常会对表，数据透视表或范围进行大量过滤，以放大数据组。

I had been in the habit of using the controls at the top of the table to select or clear filters. Doing this repetitively became frustrating, though.

我习惯于使用表格顶部的控件来选择或清除过滤器。 但是，重复进行此操作变得令人沮丧。

使用右键单击过滤器 (Use the Right-Click Filters)

I noticed that the Right-Click popup menu (the Context menu) has a group for Filter, and I could use items like Filter By Selected Cell’s Value.

我注意到右键单击弹出菜单(上下文菜单)有一个用于过滤器的组，我可以使用“ 按选定单元格的值过滤”之类的项目。

A little side benefit – if you use this command on a range which does not have filters applied, it applies filters automatically, as part of the Filter By Selected Cell’s Value operation.

一点好处–如果在没有应用过滤器的范围内使用此命令，则它会自动应用过滤器，这是“按选定的单元格的值进行过滤”操作的一部分。

This made the repetitive actions more efficient, but not enough. You need to:

这使重复动作更有效，但还不够。 你需要：

1. Right-click on a cell
   右键单击一个单元格

2. Find the Filter command, and point to it

   找到过滤器命令，并指向它

3. Find the Select by Selected Cell’s …  that you need, and click on it.

   找到所需的“ 按选定单元格选择” ，然后单击它。

I think it’s this extra cognitive effort that slows me down, not just the number of clicks involved.

我认为正是这种额外的认知努力使我放慢了速度，而不仅仅是涉及的点击次数。

自定义上下文菜单 (Customize the Context Menu)

To find a more efficient solution, I looked up a piece of code at Customizing Context Menus in All Versions of Microsoft Excel, written by Ron de Bruin. (I will not regurgitate the technique for adding items to the context menus using VBA or the Custom UI Editor – Ron explains it very effectively.)

为了找到更有效的解决方案，我在Ron de Bruin编写的“ Microsoft Excel所有版本中的自定义上下文菜单”中查找了一段代码。 (我不会反驳使用VBA或“自定义UI编辑器”向上下文菜单添加项目的技术-罗恩非常有效地解释了这一点。)

Ron’s article references Ole P. Erlandsen’s add-in for Command Bar Tools to find the Context Menu Bar Names, control IDs and control image Face IDs.

Ron的文章引用了Ole P. Erlandsen的Command Bar Tools加载项来查找上下文菜单栏名称，控件ID和控件图像Face ID。

What I learned is that, while Ron explains that the “Cell” context menu has 2 different versions:

我了解到的是，Ron解释说“ Cell”上下文菜单有2个不同的版本：

• normal view
  普通视图
• page break view
  分页视图

there are 2 others that exist depending on whether the selected object is:

根据所选对象是否存在，还有其他两个对象：

• a table or
  一张桌子或
• a pivot table
  数据透视表

Ole’s addin tool was REALLY useful to figure this out. After installing, it appears on the Add-Ins tab of the Ribbon.

Ole的外接程序工具非常有用，可以解决此问题。 安装后，它会出现在功能区的“加载项”选项卡上。

Select the command to List all CommandBar controls

选择命令以列出所有CommandBar控件

Then, select a Command Bar to see its controls

然后，选择命令栏以查看其控件

From the list, I was able to identify the context menu for tables and for pivot tables, and the controls I needed in each of these (they all have slightly different specifications).

从列表中，我能够确定表和数据透视表的上下文菜单，以及每个菜单中所需的控件(它们的规格稍有不同)。

筛选器选择的示例代码 (Sample Code For Filter Selection)

Based on this information, I created code to add 2 new commands at the top of the right-click's Context menu:

基于此信息，我创建了代码，以在右键单击的上下文菜单的顶部添加2个新命令：

• Filter By Selected Cell’s Value
  按选定单元格的值过滤
• Show All (or Clear Filter)
  全部显示(或清除过滤器)

If you prefer, you could use one of the other Filter by options instead:

如果愿意，可以改用其他“筛选依据”选项之一：

• Filter by Selected Cell’s Color
  按选定单元格的颜色过滤
• Filter by Selected Cell’s Font Color
  按选定单元格的字体颜色过滤
• Filter by Selected Cell’s Icon
  按所选单元格的图标过滤

I’ve installed the code in my Personal Macro Workbook (personal.xla or personal.xlsb), and added code in the ThisWorkbook module, to activate the function when the personal workbook is loaded (this is commented out in the sample file).

我已经在个人宏工作簿中安装了代码(personal.xla或personal.xlsb)，并在ThisWorkbook模块中添加了代码，以在加载个人工作簿时激活该功能(此示例文件中对此进行了注释)。

As a result, I now have context menus that allow for filter selection (and clearing) with only one extra click after right click.

结果，我现在有了上下文菜单，这些菜单允许在右键单击后仅单击一次即可选择(和清除)过滤器。

Here is the Context menu for a named table:

这是命名表的上下文菜单：

And here is the Context menu for a pivot table:

这是数据透视表的上下文菜单：

下载样本文件 (Download the Sample File)

To see AlexJ's code, and copy it to your own file, please visit the AlexJ's Sample Files page on my Contextures website.

要查看AlexJ的代码并将其复制到您自己的文件中，请访问我的Contextures网站上的AlexJ的示例文件页面 。

In the VBA section, look for VB0002 – Customize Context Menus.

在“ VBA”部分中，查找“ VB0002 –自定义上下文菜单” 。

Copy the code from both the ThisWorkbook module, and the mCustomPopup module.

从ThisWorkbook模块和mCustomPopup模块复制代码。

翻译自: https://contexturesblog.com/archives/2015/04/02/customize-excel-context-menus/

olami 自定义上下文