Sharing A Custom Ribbon Tab Among Workbooks

The history of posts on the Ribbon can be found at the Ribbon Example Table of Contents.

This blogpost deals with a slightly more dynamic use of RibbonX than we've dealt with in the past here. Specifically, this post illustrates how to create a tab on the ribbon, and add items to it from other workbooks. To do this, we're going to need to create two files, the first of which will be an .xlam Add-in file. Why an Add-in? I hope to demonstrate that by the end of the post.

Creating the Add-in

First, we're going to head into Excel, create a brand new workbook, and open up the Visual Basic Editor (VBE). Add a new standard module, and place the following code inside it:

[vb]Private Sub rxHost_Buttons(Control As IRibbonControl)
'Purpose : Manage the button events

Select Case Control.ID
Case Is = "rxHost_Btn1"
MsgBox "I was called from " & ThisWorkbook.Name
Case Else
'Placeholder for other macros
End Select
End Sub[/vb]

Next, save the file under whatever name you want. I chose "UI Host.xlam". Make a note of where you saved the file, as you'll need to reference it a couple of times. (For ease of testing, I just saved it to my desktop.)

Close Excel, and open up the XML for the file using the CustomUI editor tool that is list in the Ribbon Example Table of Contents page. Once you have done that, paste the following XML into the file:

[xml]




26 thoughts on “Sharing A Custom Ribbon Tab Among Workbooks

  1. I always worked with an add-in and assumed that the custom tab should also work with Personal.xlsb but you are right, it doesn't.

    I just discovered though that when you unhide Personal.xlsb the custom Ribbon tab is also revealed.

    So your conclusion is the right one and now we know why.

    Willy

  2. Hi,

    I have a problem related to AddIn in Excel 2007. I have toolbar written in VBA which loads in earlier version of Office(2003). But when i load the same application in 2007 it gives a Security Warning like Macro not trusted.

    However when i add the location from where it loads to the trusted location(Option,Trust Center,Trusted Location) there is no security warning in subsequent runing of Excel 2007 application.

    I would like to know how can i overcome this problem. Is there way we can automate this programmaticaly?

    Regards,
    Digambar.

  3. Hi Digambar,

    I'm curious as to why you see this as a problem? Typically, the Add-ins Library folder is a trusted location. I assume that you're saving your add-in in a different location?

    I haven't tried manipulating this setting via code, but I'd suspect that this is by design, much like the fact that we cannot change the "Trust Access to Visual Basic Project". If you think about it, this makes sense. If we were able to code and bypass the Trust Center settings, it makes the whole concept pretty much useless, since any nefarious programmer could do the same.

  4. Hi Ken,

    In the "Adding IdQ To The Buttons" section above, the first two buttons don't work, not the last two. The reason: they are placed into an uninhabited namespace - the namespace that is not a "native" one for a COM add-in or an Excel add-in. I think the Ribbon determines that the control's namespace is "free" and doesn't trigger callbacks. However, if you place the buttons into an inhabited namespace, the Ribbon shows an error message (verified on COM add-ins).

    For a COM add-in, the native namespace is the add-in's ProgId, for an Add-in Express add-in it may look like "MyAddin1.AddinModule". I don't know about Excel add-ins. Do you?

    We are about to introduce the "sharing Ribbon
    container controls" functionality for Ribbon components in our Add-in Express product line. There is no
    problem to share a Ribbon tab, group, box, and buttonGroup using a non-inhabited namespace.
    As to Office 2007 menus, it seems there is no way to show an image for the menu shared via an uninhabited namespace. Looks like Ribbon images can be loaded via callbacks only. May I ask you to notify me if you find that my last statement is incorrect?

    Regards from Belarus,

    Andrei Smolin
    Add-in Express Team Leader
    http://www.add-in-express.com

  5. Hi Andrei!

    Thanks for dropping by and posting your comments. In short, I don't know the answer to your questions, no, but you've given me something to work with to find out. I do appreciate it, and if (when) I learn anything on it, I'll drop you a line. 🙂

    Cheers!

    Ken

  6. Hi Ken,

    My problem is a very strange one.

    When i open the .xslm file the first tab is highlighted. There is a button on that sheet that saves the information to a new workbook. That first tabs buttons work fine. If i click on any other tab the buttons do not work what so ever. The weird thing i cant get my head around is: When i open the first .xlsm file if i click on each of the tabs then click the button to save to a new workbook these tabs work fine in the newly saved workbook. What is going on? It seems like clicking the tabs before the button only will allow them to work.

    Cheers,
    Ross

  7. Hi Ross,

    To be honest, I'd have to see your workbook to give you an answer. Are you trying to set this up using a shared namespace, or some other manner?

  8. I will try and explain what happens within the workbook because this is for a commercial purpose.

    When you open the file you are given a front page. On this page are a few cells where you can type in the customers name and address, etc. A button is on this worksheet that allows you to start a new quote. What happens is its runs through some code to create a new workbook with the customers name as the title and closes the previous workbook.

    The ribbon bar allows the user to select a Pricing Sheet, Charts Sheet, Brings up Help Dialogs, etc. There are a few buttons on each tab allowing the user to easily navigate through the program.

    So back to the actual problem. All i have done is with CustomUI set up different tabs with different buttons. Ive then generated callbacks, copied these into the programs VBA editor and assigned a macro to them such like this:

    'Callback for btnretention onAction
    Sub Macro7(control As IRibbonControl)
    Retention
    End Sub

    This just goes to a sheet called Retention

    If i click on the tab that holds this button and macro, when i save and start a new quote with clicking the button. On the new workbook this will work but only this tab. If i havent selected the tab and just type in the customers details the ribbon button wont work. The code is still there for that button but it seems like its loses something.

    Help 😀 If you need me to send any code through then email me and i will see what i can do.

    Cheers,
    Ross

  9. Hi Steven,

    Did you delete it by unloading all of your add-ins? The Tab should only be visible when you actually have an add-in loaded, and typically only with those that use 97-2003 commandbar menus. (Unless your programmer has actually decided to add their code to the Add-ins tab.) The only other thing that I can think of that would cause this is if you have a file that has deliberate code to hide the add-ins tab.

  10. Actually, the add-ins tab is available when Excel 2003-style menu/toolbar customizations have been implemented. You don't need an add-in for such customizations, just the VBA code in a workbook that creates the customizations.

  11. Hi Ken and others,

    I've found this page and the one on dynamic menus useful, thanks.

    Is it possible to have the whole menu (including tabs) load up at run time. eg the user logs in, then a query returns user specific xml for everything between the 2 Ribbon tags from a database and Excel displays it.

    I can see how this works using GetContent for dynamicmenu items, but could not see how to do the same for tabs.

    Thanks

    Duncan

  12. I can now see that I can use the getVisible callback in conjunction with the ribbon InvalidateControl method to hide/show tabs during runtime. Thus I can store user specific visibility flags in the DB and pull back those in the query instead of XML.

    Cheers

    Duncan

  13. Good stuff Duncan. Glad you found the site useful, and thanks for letting me know that you got your question worked out. 🙂

  14. Solution fir the Button idQ callbacks and namespace. If you want to use button idQ and need the callbacks make sure the namespace is equal to your prog.id of the add-in.

    that's it

  15. Hi there,

    That will work if you are creating your addin from Visual Studio, but the issue remains if you are trying to do all your work within an Office client. We don't have a Prog.ID for our addins, and therefore can't link to one. 🙁

  16. I want to use a button with a namesapce in a word template. When the template load the button loads but the vba code does not excute. How do I set the name space to the template that I loaded?

  17. Hi there,

    We actually cover this in detail in deployment chapter of the RibbonX book (link at left) on pages 485-491.

    I'd have to see your code to see what isn't linked up properly, but one thing that gets people with Word is that you can't use the Document_Open event with a template, you must use an AutoExec routine instead.

    Hope that helps.

  18. Hi Ken,

    Andy Pope forwarded me the link to this article in connection with a question I had posed.

    However it seems that an add-in seems to be the only way to go from this post.

    Just wondering if you had any progress in trying to get a macro to work on a shared button.

    I have 3 files which are giving me some confusion. If it is OK , I can send you a link to them along with my query.

Leave a Reply

Your email address will not be published. Required fields are marked *