CODEWITHSUNDEEP
excelvba
a_bodkin_betwixt_the_knaves
a_bodkin_betwixt_the_knaves

Reputation: 21

Sharing a Customization File (exportedUI) with different users

I created a custom ribbon in Excel, which calls several macros in my PERSONAL.XLSB, and exported the Customization File (exportedUI) to a location on a shared file server. All of the macros are saved as .bas files, which each user imports to their Personal.XLSB before trying to use the ribbon.

Everything works fine on my computer, the ribbon is accessible and all of the buttons work. But, it throws an error when another user clicks any button to utilize the Customization File from their machine.

I opened up the exportedUI file, and noticed that every action calls the PERSONAL.XLSB, which is saved on my local machine as: C:\Users\my.name\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB...

This of course, does not work when other users have it saved as: C:\Users\their.name\AppData\etc...

My only idea for a work-around right now, is to create a distinct exportedUI file for each user, but that can be quite laborious and messy, especially if I'd like to make modifications to any of the code in the future. I'd really like one file that can be shared with each user.

Is there a cleaner way to share a custom ribbon with other users on their machines? One file that each user can use?

Or am I stuck trying manipulate a file to coincide with a unique version for each user (probably through a text editor using a python script)???

Upvotes: 0

Views: 4217

Answers (1)

Profex
Profex

Reputation: 1390

I think you should be distributing the code via Excel Add-In file (.xlam).

You can attach a Ribbon to the Add-in itself, which isn't visable, much like the Personal.xlsb.

See Ron de Bruin's Change the Ribbon in Excel 2007-2016.

Just copy your code to a new file, rename it as a Excel Add-in (warning it will change your path every time you choose add-in from the drop down) and use the Custom UI Editor to create the Ribbon menu (which doesn't require a full file path).

End users can either just open the add-in file when they want to use your menus/code, or they can add it to their installed add-ins which start with Excel.

Edit...

Here is a sample UI XML from one of my add-ins. Using <ribbon startFromScratch="false"> with a namespace (nsCompany) allows me to have multiple add-in files add more buttons/menus to the same Ribbon.

<?xml version="1.0" encoding="utf-8" ?>
<!-- Ribbon for 2007+ -->
<customUI onLoad="Engineering Tools.xlam!EngRibbon.RibbonOnLoad" xmlns="http://schemas.microsoft.com/office/2006/01/customui" xmlns:nsCompany="Company Tools">
  <ribbon startFromScratch="false">
    <tabs>
      <tab idQ="nsCompany:CompanyTab" label="Company" keytip="C">
        <group id="ENGGroup1" label="PBS">
          <button id="ENGButton11" label="Project Analysis" imageMso="ImportExcel" size="large" onAction="Engineering Tools.xlam!EngRibbon.USER_ImportMLBOM" getEnabled="Engineering Tools.xlam!EngRibbon.GetEnabled" tag="EngTool" keytip="I" />
          <button id="ENGButton12" label="Login" imageMso="ServerConnection" size="large" onAction="Engineering Tools.xlam!EngRibbon.USER_Login" getEnabled="Engineering Tools.xlam!EngRibbon.GetEnabled" tag="EngTool" keytip="L" />
      </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

Upvotes: 1

Related Questions