Custom Ribbons

[Ivan Howard]

Hi All,

I have recently migrated to Excel 2010 from Excel 2003.

In Excel 2003, I created an Add-In that had 20 bespoke macros in it. Everytime a user opened Excel 2003, the Add-In was deleted and re-installed (from a shared drive) using VBA coding within the "Personal.xls" workbook. This allowed me to update the Add-In and deploy it to the team as and when needed.

Forget about the re-installing piece with regards to "Personal.xls"... what I need to do is plain and simply (haha) have a macro that either builds a custom ribbon or a custom toolbar within Excel 2010. I should be able to do the rest myself.

Outside of VBA, I have manually created a custom ribbon/tab and successfully linked the macros (stored in an Add-In file) to the respective buttons... BUT when I export this customised ribbon and then import it on a user's machine, it doesn't link up the buttons on the ribbon to the macros in the Add-In.

Any advice on creating custom ribbons would be greatly appreciated.

Thanks very much.

Ivan

 

[jimrward]

I encountered a similar problem in a networked environment however it was for a toolbar in outlook but the same cold be applied to excel, in that I had a refresh button in excel distributed once to a new user, an external text file with descriptions of buttons, icons, and macro names, as outlook is a single user environment, you still have the problem of managing code should it ever change
Let me dig out the code I used along with text file etc etc
Ribbons are a chore to deal with unless you create a file with the ribbons defined and post it to every users excel startup folder

What code have you got so far for triggering macros from ribbons especially the calling mechanisms, can you post segments of code, I will look out what I have as it was a while ago

 

[Ivan Howard]

Hi Jim,

Thanks for your reply.

This morning, I didn't have any code that triggers macros from ribbons... What I did (which I don't think is transferrable between computers) was I just created a customised ribbon and added macros to it from the "Choose a command from:" section on the Excel Options > Customize Ribbon dialogue box.

However, what I have found in research since I posted this original post was that I had to use the "Custom UI Editor" to insert an Excel 2010 XML 'module' (with XML - read on...) into the file before it will work.

This is how I have got it to work:
1. I followed the instructions on here: Load different RibbonX when opening file in Excel 2007 or 2010
2. From within the Custom UI Editor, I then customised the xml code to correspond to macro names, captions, etc.
3. Then, from within Excel 2010, I loaded the file with the 20 macros, ensuring that I put the "control As IRibbonControl" in the brackets of each macro's top row.
4. I then saved the file as an Excel Add-In and installed the Add-In and - it works!

Hope it works for you if needed.

 

[jimrward]

Cool, I managed without the editor but it relies on dissecting the xlsx file, which is a zip file of sorts, I extracted the XML bit and jiggled that then you slide the XML file back into the archive and then I moved the file to the xlstartup folder

 

[Ivan Howard]

Excellent - thanks for your help Jim.

Enjoy the rest of your day!

 

[Ivan Howard]

Cool, I managed without the editor but it relies on dissecting the xlsx file, which is a zip file of sorts, I extracted the XML bit and jiggled that then you slide the XML file back into the archive and then I moved the file to the xlstartup folder

Hi Jim, I hope you're well.

I am having issues with being able to edit the XML part of my Excel Add-In as I cannot install the "Custom UI Editor for Microsoft Office" on my new work machine. Please could you kindly explain how you extracted the XML, edited it (in what application?) and then slid it back into an archive?

Alternatively, I have Notepad++ installed on my new work machine but cannot open the Add-In with it. I'm not sure how to just open the XML part of the Add-In file. Any advice?

Many thanks Jim and anyone else who can help.

 

[RoryA]

You can rename the add-in with a .Zip extension, then extract the CustomUI part from the zip file, edit it and then put it back. Then just rename back to .xlam

 

[Ivan Howard]

You can rename the add-in with a .Zip extension, then extract the CustomUI part from the zip file, edit it and then put it back. Then just rename back to .xlam

Thanks very much Rory. For years I have used the Custom UI Editor! #MindBlown

Thanks very much.

 

[RoryA]

To be fair, the editor is a lot easier!

 

[Ivan Howard]

To be fair, the editor is a lot easier!

Yes it is but thankfully now I don't need to struggle on the new machine. Enjoy the rest of your day.