vba 是否有 Project 的 SetCustomUI for Excel 的替代方案？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16264003/
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
Is there an alternative to Project's SetCustomUI for Excel?
提问by Philippe Signoret
I have an Excel worksheet with a bunch of normal VBA macros. This file is constantly being updated and distributed as an Excel Add-In (.xlam). So far, I'm very happy with how this works.
我有一个 Excel 工作表，里面有一堆普通的 VBA 宏。此文件不断更新并作为 Excel 加载项 (.xlam) 分发。到目前为止，我对它的工作方式感到非常满意。
Now I want to add a ribbon tab and buttons which run some of these macros. Originally, I was excited to find this MSDN article, but then dismayed that SetCustomUIdoesn't seem to exist for Excel (only Project), so I wouldn't be able to simply use that in Workbook_Open. Other SO questions confirm this, but don't give an alternative.
现在我想添加一个功能区选项卡和运行其中一些宏的按钮。最初，我很高兴找到这篇 MSDN 文章，但随后感到沮丧的是SetCustomUIExcel（仅 Project）似乎不存在，所以我无法简单地在Workbook_Open. 其他 SO 问题证实了这一点，但不要提供替代方案。
My requirements:
我的要求：
- Users must have access to the macro VBA code like they do today (the add-in appears as a project in the VBE).
- Updating and redistributing the add-in must be easy. Today, I send them a file to update.
- I want a ribbon tab with buttons for some of the macros.
- 用户必须像现在一样访问宏 VBA 代码（加载项在 VBE 中显示为项目）。
- 更新和重新分发加载项必须很容易。今天，我向他们发送了一个文件进行更新。
- 我想要一个带有某些宏按钮的功能区选项卡。
Any ideas?
有任何想法吗？
回答by David Zemens
Here is some code that I have been using on AddIn files for a while. I inherited it from someone else, but it has always worked well enough for me.
这是我在 AddIn 文件上使用了一段时间的一些代码。我从别人那里继承了它，但它对我来说一直很好用。
It should create a new toolbar in the AddInsribbon:
它应该在AddIns功能区中创建一个新工具栏：
I think I copied all of the pertinent code. Let me know if you have questions or run in to any problems.
我想我复制了所有相关的代码。如果您有任何疑问或遇到任何问题，请告诉我。
Option Explicit
'This module contains functions and subroutines to create Add-in menus
Public Const MenuName As String = "Menu Name"
Public Const APPNAME As String = "&Menu Name"
Private Sub Credit_Inf()
MsgBox "Created by YOUR NAME"
End Sub
Private Sub Auto_Open()
Dim NewMenuItemMacro As String
Dim NewMenuItem As String
Dim XLCommandBar As Integer
Dim NewItem As CommandBarButton
Dim ToolsMenu As CommandBarPopup
Dim NewMenu As CommandBar
NewMenuItemMacro = MenuName
NewMenuItem = APPNAME & "..."
XLCommandBar = 1 'Worksheet Menu Bar
'Delete the current menu if it exists (just in case)
On Error Resume Next
CommandBars(MenuName).Delete
On Error GoTo 0
Set NewMenu = Application.CommandBars.Add(MenuName, msoBarTop)
' .....
NewMenu.Visible = True
' Create a popup control on the bar and set its caption.
Set ToolsMenu = NewMenu.Controls.Add(Type:=msoControlPopup)
ToolsMenu.Caption = "Who built this?"
ToolsMenu.BeginGroup = True
With ToolsMenu.Controls.Add(Type:=msoControlButton)
.OnAction = "Credit_Inf"
.Caption = "Find out who built this"
.FaceId = 99
.Style = msoButtonCaption
.BeginGroup = False
End With
'##Repeat ToolsMenu.Controls.Add, as necessary
End Sub
Private Sub Auto_Close()
'Delete the current menu if it exists (just in case)
On Error Resume Next
CommandBars(MenuName).Delete
On Error GoTo 0
End Sub
Private Sub EnableMenuItem(sItem As String, bEnable As Boolean)
On Error GoTo Err_EnableMenuItem
Dim NewItem As CommandBarButton
Dim NewMenu As CommandBar
Set NewMenu = Application.CommandBars(MenuName)
Set NewItem = NewMenu.FindControl(Tag:=sItem, recursive:=True)
NewItem.Enabled = bEnable
Err_EnableMenuItem:
Resume Next
End Sub
Public Function IsWorkbookOpen() As Boolean
IsWorkbookOpen = True
If Application.Workbooks.count = 0 Then
IsWorkbookOpen = False
End If
End Function
回答by RBarryYoung
You should be able to just embed your ribbon CustomUI in your XLAM file. I believe that Microsoft has a tool available to assist with this (though you can make and embed it manually as well).
您应该能够将您的功能区 CustomUI 嵌入您的 XLAM 文件中。我相信微软有一个工具可以帮助解决这个问题（尽管你也可以手动制作和嵌入它）。
From the OP, here is what they did to accomplish this, using the Office Custom UI Tool:
在 OP 中，这是他们使用Office 自定义 UI 工具完成的操作：
I've tried what this answer recommends: Use a tool (I used Custom UI Editor Tool) in the original worksheet to add a tab and buttons, then save as Add-In. This works perfectly for what I wanted.
我已经尝试了这个答案推荐的内容：在原始工作表中使用一个工具（我使用了自定义 UI 编辑器工具）来添加一个选项卡和按钮，然后另存为加载项。这非常适合我想要的。
Here's the setup:
这是设置：
Module mdlMyActions in MyAddin.xlsm:
Public Sub HelloWorld(ctl As IRibbonControl)
MsgBox("Hello, world!")
End Sub
XML inserted into MyAddin.xlsm:
插入到 MyAddin.xlsm 中的 XML：
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon startFromScratch="false">
<tabs>
<tab id="tabMyTab" label="My Tab">
<group id="grpMyGroup" label="My Group">
<button id="btnHelloWorld" label="Hello World"
imageMso="HappyFace" size="large" onAction="HelloWorld" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>
Note that this is all before saving as an Add-In. Once saved as add-in and installed into Excel, the add-in works perfectly.
请注意，这是在保存为加载项之前的全部内容。一旦另存为加载项并安装到 Excel 中，加载项即可完美运行。
