Customizing Ribbon Excel 2010

[ryancgarrett]

I'm just finalizing a VBA application I've been working on for some time. I was about to make a Table of Contents sheet with links to various sheets and a bunch of buttons to run various macros I've written when it occurred to me it would be really nice to have instead a tab on the ribbon where I can organize my macros into groups and have buttons to run them. I've looked into it a bit and am a bit perplexed as to how ribbon customization works. It seems that it is all written in XML and I can't figure out how to create buttons to call my macros. Is there a fairly straightforward way of doing this or am I in way over my head?

 

[xenou]

You have to wrap your head around a few things but overall it's not hard. Keep plugging away and you'll get there. There's a pretty good write up here:
Excel Ribbon - Add Custom Tab

Note that she provides a link at the top under files and tools required: the UI Editor. You want to use that - much easier that way.

Ron Debruin provides additional examples and sample files:
Change the Ribbon in Excel 2007 - 2013

Once you get going you will also find it useful to have the icons gallery (there is a link on this page):
Ribbon Examples files and Tips


I suppose some of the gotchas are:

• XML is CaSe SeNsItIvE
• XML must be valid. One missing angle bracket and your customization is broken.
• You have to be careful about not duplicating ID's, which have to be unique in the XML file, if you give ID's to your custom groups or tabs.
• The macros are written a little differently - don't forget to provide the ribbon callback to the macros arguments.

P.S., don't forget about shortcut keys and right-click menus either. These can be great "at your fingertip" methods of invoking your macros. I did a write up on the right click menus here: http://www.mrexcel.com/forum/excel-...izing-excel-right-click-menu-still-works.html

 

[ryancgarrett]

Thanks for all the info xenou. I read the articles and found some great tips. One question though: Do I need to change all of the macros I have written to sub macroName(control As IRibbonControl) in order for them to work when called from the ribbon, and does this have any other effects on my code?

Thanks again for your help!

 

[xenou]

Yes, I believe that you must have the control argument in your macro or they just don't work - I've never done a lot with this argument, but I believe you can get information about the control (if it was a checkbox was the value true or false, etc), and possibly even carry information back to the control when the macro is complete.

Sometimes it's useful to set up a simple macro to be the hook and have it call the real macro:

Sub Go_Print_Order(cntrl as IRibbonControl)
    Call Print_Order
End Sub

Not sure why I think that though. I usually have all of my menu customizations in one module. Then if I need to change something having to do with the menus I know where to look. That probably made more sense pre-ribbon. It's quite simple on the VBA side now.