Page tree

Welcome to FreeSoftwareServers Confluence Wiki

Skip to end of metadata
Go to start of metadata

Update: You can use a shortcut in the XLSTART folder! This allows users to pull the latest update upon each excel start and only one location to control/push updates too.

Update: This method is now my preferred way, it can be a bit more complicated during setup, but it is also great for organizational reasons. I no longer have to update an "Excel.OfficeUI" each time I deploy a new macro, I include the new ribbon button inside the "xlam". This also means I'm not overwriting users ribbon, thought I've never had anyone complain, its better to avoid that now!

A few things of note:

Use this tool → https://github.com/fernandreu/office-ribbonx-editor/releases/latest

Notes: https://github.com/fernandreu/office-ribbonx-editor

Read This Blog: https://www.rondebruin.nl/win/s2/win001.htm

Understand "idQ" NOTE THE Q in XML's if you want to follow my idea of having individual xlam add-in's under the same custom tab/group etc. → https://stackoverflow.com/questions/3052229/add-a-custom-ribbon-group-to-an-existing-custom-ribbon-group-in-word-2007

Read more about the CustomUI XML Schema → https://www.microsoft.com/en-us/download/details.aspx?id=1574 

More Reading: https://gregmaxey.com/word_tip_pages/customize_ribbon_main.html

You can add custom icons and use the tag "image" vs "imageMso".

List of options for imageMso→ https://bert-toolkit.com/imagemso-list.html or https://www.microsoft.com/en-us/download/confirmation.aspx?id=21103

Note: The Macro you call must have this "control statement" at the end!

Public Sub Foo(control As IRibbonControl)

TLDR;

FileA.xlam:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" xmlns:Accounting="foobar">
  <ribbon>
    <tabs>
      <tab idQ="Accounting:Accounting" label="Accounting" insertBeforeQ="TabInsert" keytip="L">
        <group id="GroupA" label="Group_A_VisibleLabel">
          <button id="HelloWorldA" label="Greetings" size="large" onAction="HelloWorld" />
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>
Public Sub HelloWorld(control As IRibbonControl)
MsgBox "HelloWorld"
End Sub

FileB.xlam:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" xmlns:Accounting="foobar">
  <ribbon>
    <tabs>
      <tab idQ="Accounting:Accounting" label="Accounting" insertBeforeQ="TabInsert" keytip="L">
            <group idQ="Accounting:GroupB" label="Group_B_VisibleLabel">
              <button id="ButtIdsDontMatter" label="ButtonLabelsMatter" size="large" onAction="FooBar" imageMso="HappyFace" />
            </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>
Public Sub FooBar(control As IRibbonControl)
MsgBox "FooBar"
End Sub

FileC.xlam:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" xmlns:Accounting="foobar">
  <ribbon>
    <tabs>
      <tab idQ="Accounting:Accounting" label="Accounting" insertBeforeQ="TabInsert" keytip="L">
            <group idQ="Accounting:GroupB" label="Group_B_VisibleLabel">
              <button id="ButtIdsDontMatter" label="FileCButton" size="large" onAction="FileCSub" imageMso="HappyFace" />
            </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>
Public Sub FileCSub(control As IRibbonControl)
MsgBox "FileCSub"
End Sub

Result:

I take it a step further and have a synced directory in OneDrive via Sharepoint/Teams and use a shortcut pasted into local XLSTart folders:

@echo off
pushd %~dp0
cls
::FreeSoftwareServers.com

set XLSTART=%AppData%\Microsoft\Excel\XLSTART
set OFFICEUI=%userprofile%\AppData\Local\Microsoft\Office
set USERDIR=%USERPROFILE:C:\Users\=%
set TEMP=%TEMP%

IF EXIST "%XLSTART%" GOTO FOUNDXL
md "%XLSTART%"
:FOUNDXL

IF EXIST "%OFFICEUI%" GOTO FOUNDUI
md "%OFFICEUI%"
:FOUNDUI

del "%XLSTART%\*.lnk" /s /f /q
copy "%~dp0\Excel\file.xlam - Shortcut.lnk" "%XLSTART%"
copy "%~dp0\Excel\UIAutomationClient.dll" "%XLSTART%"



  • No labels