vba 永久自定义功能区

Question

提问by user3709812

I have written a bunch of macros to make repetitive tasks in the office less time-consuming. I'm constantly editing them, adding functionality and tweaking. Each time, I then have to go around to each employee in the office, unhide their PERSONAL.xlsb file, remove the old module, add the new and updated module.

我编写了一堆宏来减少办公室中重复性任务的耗时。我一直在编辑它们，添加功能和调整。每次，我都必须去办公室的每个员工那里，取消隐藏他们的 PERSONAL.xlsb 文件，删除旧模块，添加新的和更新的模块。

These macros are controlled from a custom tab on the ribbon, with 15 or so buttons, each triggering a macro. Every now and then, I have to add/remove/change buttons on the custom tab, which then has to be done for everyone in the office individually.

这些宏是从功能区上的自定义选项卡控制的，有 15 个左右的按钮，每个按钮触发一个宏。时不时地，我必须在自定义选项卡上添加/删除/更改按钮，然后必须为办公室中的每个人单独完成。

I was looking into scripting the buttons onto the ribbon, but that doesn't seem possible from what I've read. I ended up making a customui xlsm file, from a tutorial I saw on this site, but this is no good, because the Custom Tab disappears if the xlsm workbook is hidden, or not open.

我正在研究在功能区上编写按钮的脚本，但从我读过的内容来看，这似乎是不可能的。我最终根据我在本网站上看到的教程制作了一个 customui xlsm 文件，但这不好，因为如果 xlsm 工作簿隐藏或未打开，自定义选项卡就会消失。

Basically, I want to be able to go to a computer, copy across PERSONAL.xlsb and CUSTOMRIBBON.xlsm into the XLSTART folder, and have it all ready to go straight away.

基本上，我希望能够访问计算机，将 PERSONAL.xlsb 和 CUSTOMRIBBON.xlsm 复制到 XLSTART 文件夹中，然后立即准备好一切。

Is there a way? or is it just not possible...

有办法吗？或者只是不可能...

Answer 1

回答by

Disclaimer: credit for this answer goes to Ron de Bruin.

免责声明：此答案归功于 Ron de Bruin。

You don't really need to separate files. All you need is one file that stores the ribbon and the code. Follow the below to see how this works...

你真的不需要分开文件。您所需要的只是一个存储功能区和代码的文件。按照下面的操作来看看它是如何工作的......

Follow Ron de Bruin's tutorialon adding controls to Ribbon using Custom UI Editors for Microsoft Office.

按照Ron de Bruin 的教程使用Microsoft Office 的自定义 UI 编辑器向功能区添加控件。

It's as simple as

就这么简单

Create a workbook and save it as Add-in CustomRibbon.xlamand put that file in the C:\Users\yourUserName\AppData\Roaming\Microsoft\AddIns\
download Custom UI Editors for Microsoft Office. When you open it you want to load the CustomRibbon.xlamand right click it and select Office 2010 Custom UI

创建一个工作簿并将其另存为加载项CustomRibbon.xlam并将该文件放入C:\Users\yourUserName\AppData\Roaming\Microsoft\AddIns\
下载Microsoft Office 的自定义 UI 编辑器。当您打开它时，您要加载CustomRibbon.xlam并右键单击它并选择Office 2010 Custom UI

enter image description here

在此处输入图片说明

On the right hand side of the window paste the below XML

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon>
    <tabs>
      <tab idMso="TabHome" >
        <group id="customGroup1" label="My Group" insertAfterMso="GroupEditingExcel">
          <button id="customButton1" label="Click Me" size="large" onAction="Macro1" imageMso="HappyFace" />
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

Save changes and close the program.
Open Excel and you should see the new icon on your ribbon

在窗口的右侧粘贴以下 XML

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon>
    <tabs>
      <tab idMso="TabHome" >
        <group id="customGroup1" label="My Group" insertAfterMso="GroupEditingExcel">
          <button id="customButton1" label="Click Me" size="large" onAction="Macro1" imageMso="HappyFace" />
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

保存更改并关闭程序。
打开 Excel，您应该会在功能区上看到新图标

enter image description here

在此处输入图片说明

You can assign a macro to the button in your CustomRibbon.xlambut you would have to directly open the CustomRibbon.xlamand open VBE and insert a module. Then in the module stick the following

您可以为您的按钮分配一个宏，CustomRibbon.xlam但您必须直接打开CustomRibbon.xlam并打开 VBE 并插入一个模块。然后在模块中粘贴以下内容

Sub Macro1(control As IRibbonControl) MsgBox "Hi There" End Sub

again, save and close. Note that in the XML you have assigned a value to the OnActionproperty to Macro1. Obviously with more buttons and more macros this where you assign the trigger for each button.
Turn on developer tab, click on Add-ins and make sure your CustomRibbonis turned on
Now open Excel (not CustomRibbon ) and when you click the smiley facebutton on the ribbon the code in Macro1should execute showing a Message Box

再次，保存并关闭。请注意，在 XML 中，您已为OnAction属性分配了一个值Macro1。显然有更多的按钮和更多的宏，你可以为每个按钮分配触发器。
打开开发人员选项卡，单击加载项并确保您CustomRibbon已打开
现在打开 Excel（不是 CustomRibbon ），当您单击功能区上的笑脸按钮时，代码Macro1应该执行并显示一个消息框

So as you can see, if you put all your business logic and the ribbon's XML in the CustomRibbon.xlamand save it as an add-in on each machine then when you update the original CustomRibbonall you have to do on each client is just overwrite the CustomRibbonfile with a new one.

所以你可以看到，如果你把所有的业务逻辑和色带的在XMLCustomRibbon.xlam并保存它作为一个附加的每一台机器上，那么当你更新原来CustomRibbon所有您需要每个客户端上做的仅仅是覆盖CustomRibbon文件，一个新的。

I hope this is the solution you were looking for.

我希望这是您正在寻找的解决方案。

Answer 2

回答by excelSU

For anyone still having issues with this:

对于仍然有此问题的任何人：

As stated above the CustomRibbon.xlam file should be saved/located in C:\Users\yourUserName\AppData\Roaming\Microsoft\AddIns\

如上所述，CustomRibbon.xlam 文件应保存/位于 C:\Users\yourUserName\AppData\Roaming\Microsoft\AddIns\

I suspect anyone not having luck with this was missing some of the required fields of XML as per this site on CustomUI:

我怀疑没有运气的人会根据CustomUI上的这个站点遗漏一些 XML 的必填字段：

Reading the Custom UI Code

阅读自定义 UI 代码

In the xml code, you can see that there is a heading for each part of the custom UI:

在xml代码中，可以看到自定义UI的每个部分都有一个标题：

Namespace
Ribbon
Tabs
Group
Button

命名空间
丝带
标签
团体
按钮

Each item has a unique ID, a label, and perhaps other properties, such as an icon and a macro that runs when the button is clicked.

每个项目都有一个唯一的 ID、一个标签，可能还有其他属性，例如图标和单击按钮时运行的宏。

For example, here is the code for one of the buttons in the Contoso Tools group. Line breaks were added, to make the code easier to read.

例如，下面是 Contoso 工具组中按钮之一的代码。添加了换行符，使代码更易于阅读。

Excel Ribbon Custom button code

Excel 功能区自定义按钮代码

The ID -- customButton1 -- is unique. Each ID can only used once in the code.
The label -- ConBold -- is the text that will appear on the button in the Ribbon tab.
The image -- Bold -- will also appear on the button
The size is set to Large, so it will be easy to see on the Ribbon.
The onAction -- conBoldSub -- is the name of the macro that will run when the button is clicked.

ID -- customButton1 -- 是唯一的。每个 ID 在代码中只能使用一次。
标签 -- ConBold -- 是将出现在功能区选项卡按钮上的文本。
图像 -- 粗体 -- 也将出现在按钮上
大小设置为大，因此在功能区上很容易看到。
onAction -- conBoldSub -- 是单击按钮时将运行的宏的名称。

it was causing issues since some of the required fields (customUI, ribbon, tabs..) of the code was cut off in the posted answer above, the whole complete code that works is:

由于代码的某些必填字段（customUI、ribbon、tabs ..）在上面发布的答案中被切断，因此导致问题，整个完整的代码是：

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon startFromScratch="false">
        <tabs>
            <tab idMso="TabHome" >
                    <group id="customGroup1" label="My Group" insertAfterMso="GroupEditingExcel">
                    <button id="customButton1" label="Click Me" size="large" onAction="Macro1" imageMso="HappyFace" />
                    </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

Once all the required XML fields are present it should work as expected. At this point follow the original answers instructions and it should work without a hitch.

一旦所有必需的 XML 字段都存在，它应该可以按预期工作。此时请按照原始答案说明进行操作，它应该可以顺利运行。

Answer 3

回答by Shashiraju

They have recently updated custom UI editorsoftware to support all version of MS office as earlier version was not properly supporting for upper versions (Office 2010+ )

他们最近更新了自定义 UI 编辑器软件以支持所有版本的 MS Office，因为早期版本无法正确支持更高版本（Office 2010+）

Now the software is renamed as OFFICE RIBBONX EDITORand its available for download

现在该软件更名为OFFICE RIBBONX EDITOR并可供下载

Here is the links for more information and Download:

以下是更多信息和下载的链接：

https://www.rondebruin.nl/win/s2/win001.htm

https://github.com/fernandreu/office-ribbonx-editor/releases/latest

Big thanks to 'Ron de Bruin', 'Fernando Andreu' and 'Trang Luu'

非常感谢“Ron de Bruin”、“Fernando Andreu”和“Trang Luu”

vba 永久自定义功能区

提问by user3709812

回答by

回答by excelSU

回答by Shashiraju

相关推荐

vba 增加 Microsoft Access 2013 中消息框的字体大小

vba Excel 找不到日期（无论格式选项如何）

vba 如何从左到右对列进行排序？

vba 将UTC时间转换为本地时间

相关推荐

最近更新

标签