vba 在 Excel 中自动添加加载项的单个菜单项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11682941/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Automatically add a single menu item of an add-in in Excel
提问by SoftTimur
I am using Microsoft Excel 2010 for Windows.
我在 Windows 上使用 Microsoft Excel 2010。
I have already developed an add-in addin.xlam, which contains a sub main. addin.xlamis at the right place so that it is visible and selectable via the menu Developer -> Add-Ins. When I open a normal workbook test.xlsm, and press Alt + F11, I can see the code of addin.xlamis loaded.
我已经开发了一个 add-in addin.xlam，其中包含一个 sub main。addin.xlam位于正确的位置，以便通过菜单可见和选择Developer -> Add-Ins。当我打开一个普通的工作簿test.xlsm，然后按Alt + F11，我可以看到addin.xlam加载的代码。
My aim is to add a single menu item to the menu bar of Excel, to allow users to launch mainof add-in.xlam. By following this link, my code in addin.xlamis as follows:
我的目标是单一的菜单项添加到Excel的菜单栏，让用户推出main的add-in.xlam。通过点击此链接，我的代码addin.xlam如下：
Option Explicit
Dim cControl As CommandBarButtonPrivate
Sub Workbook_AddinInstall()
On Error Resume Next 'Just in case
'Delete any existing menu item that may have been left.
Application.CommandBars("Worksheet Menu Bar").Controls("Super Code").Delete
'Add the new menu item and Set a CommandBarButton Variable to it
Set cControl = Application.CommandBars("Worksheet Menu Bar").Controls.Add
'Work with the Variable
With cControl
.Caption = "Super Code"
.Style = msoButtonCaption
.OnAction = "main" 'Macro stored in a Standard Module
End With
On Error GoTo 0
End Sub
Private Sub Workbook_AddinUninstall()
On Error Resume Next 'In case it has already gone.
Application.CommandBars("Worksheet Menu Bar").Controls("Super Code").Delete
On Error GoTo 0
End Sub
This code is well placed in ThisWorkbookof addin.xlam, it is also visible in test.xlsm. But I can't see any change in the menu bar.
这段代码很好地放在ThisWorkbookof 中addin.xlam，它也可以在test.xlsm. 但是我在菜单栏中看不到任何变化。
Does anyone know what happens?
有谁知道会发生什么？
回答by Charles Williams
The AddinInstall and AddinUninstall events are only fired when the the addin is "installed" or "uninstalled" using the Excel Addin Manager.
只有在使用 Excel 插件管理器“安装”或“卸载”插件时才会触发 AddinInstall 和 AddinUninstall 事件。
IMHO this can lead to problems, so I always recommend using the Workbook_Open and Workbook_BeforeClose events instead.
恕我直言，这可能会导致问题，所以我总是建议改用 Workbook_Open 和 Workbook_BeforeClose 事件。
回答by peng W.
Charles is right, you need to replace Workbook_AddinInstall()with Workbook_Open(), and replace Workbook_AddinUninstall()with Workbook_BeforeClose().
Charles 是对的，您需要替换Workbook_AddinInstall()为Workbook_Open()，然后替换Workbook_AddinUninstall()为Workbook_BeforeClose()。
furthermore, you need CommandBarButtonnot CommandBarButtonPrivate.
此外，你需要CommandBarButton不CommandBarButtonPrivate。
good luck!
祝你好运！
