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]
Note in the above XML that we've added some pieces to the XML. Specifically:
- xmlns:Q="My Shared Ribbon"
- This line gives us a way to link each XML namespace between add-ins. It must be the same in each file that is going to share the Ribbon items that you have created.
- tab idQ = "Q:rxTabUI"
- We're now using idQ instead of just id. This refers to our qualified namespace, and gives us a way to refer to our own controls across files, as you'll see later.
Save the XML, re-open Excel, and activate the Add-in. (Office|Excel Options|Addins|Go|Browse). Make sure it gets checked. Once done, you should now see a new "UI Test" tab on the Ribbon (after the View tab). Clicking on that should show you the following group:
Clicking the button will tell us the name of the file that the button was created in. ("UI Host.xlam" for me.)
Accessing This Tab From Another Workbook
Since we're already in Excel, let's create another new workbook, and drop the following code into a standard module:
[vb]Private Sub CallControl(Control As IRibbonControl)
'Purpose : React to the button click and inform the user where
' it was called from
MsgBox "You clicked " & Control.ID & " from " & ThisWorkbook.Name
End Sub[/vb]
(Yes, the code is pretty inane, but if you're following all this, I expect that you can write your own to do something useful. If not, read some more of the posts here, or email me.)
Now that the code is there, save the file as a macro enabled file (xlsm) and close it. (For the demo, I called the file "Leech.xlsm" for want of a better name.)
Open the file up in the CustomUI editor, and add the following XML code:
[xml]
Some things to note about this XML:
- We're using the same xmlns:Q="My Shared Ribbon" as our Host add-in.
- We also point our code to tab idQ = "Q:rxTabUI", as this is now a shared tab that we want to add controls to.
- We've added a new group to our tab, and by way of the insertBeforeQ="Q:rxGrpUI" argument, we have asked for it to show up before the Host add-in's native group
Save the XML code, go back to Excel, and open the file. The "UI Test" tab should now look as follows:
And just for reference, clicking the Button1-4 will tell you that they were called from the xlsm file. Clicking the "Click Me" button, however, will still tell you that it was called from the Host Add-in.
Navigational Effects
Interestingly enough, we have just created a workbook contextual tab. If you create a new workbook right now, you'll see that the "Leech" group disappears, and you are left with only the "UI Host" group showing, as if the "Leech.xlsm" workbook had not been loaded. Navigate back to the "Leech.xlsm" file, and the "Leech" group is re-added.
Making Your Controls Available To All Workbooks
Currently, the only way I've had success with this is to create my file as an xlam add-in. I tried a couple of different things, none of which worked well. Just as a matter of record, they are documented below:
Adding idQ to the Group:
Using the following code to specify the Leech group as a qualified id (idQ):
[xml]
I was left with the following when I went into another book:
Adding idQ To The Buttons:
The button control does have an idQ as well. I'm sure I'm missing something simple here, but I just could not get this to work. Using the XML below seemed to create everything just find, but only the second two buttons would not even fire VBA code at all. I'll need to figure this out, but for now, I can't recommend it:
[xml]
Using Personal.xlsb
While I had no issue creating a RibbonX UI and saving it in a different xlsb file, Personal.xlsb was different. It was creating the Ribbon tab, yet it was just as visible as the workbook. (i.e. It wasn't.)
Conclusion
My experiences point to add-ins as the ideal source for Ribbon UI containers if you want to share them between workbooks and make them accessible. If you want to do a lot of work with workbook contextual Ribbon adjustments, I'd suggest creating one container add-in, and having your individual workbooks add to them as they come in scope.
26 thoughts on “Sharing A Custom Ribbon Tab Among Workbooks”
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
For personal.xls I like this example
Easy to change and it is working great
http://www.rondebruin.nl/qat.htm
Great, thanks, Ron! 🙂
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.
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.
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
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
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
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?
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
Any luck or any ideas what could be happening?
Anyone?
Ross
Hi Ross,
I've emailed you. If you can send me your workbook, I'll see if I can carve out some time to take a look.
I deleted by accident the Add-In Tab from Excel and can't find a way to get it back. Any suggestions?
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.
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.
Doh! That's correct. Thanks for clarifying, Jon. 🙂
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
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
Good stuff Duncan. Glad you found the site useful, and thanks for letting me know that you got your question worked out. 🙂
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
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. 🙁
Just What I needed!!! Thank you so much
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?
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.
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.
Hi MK,
How about you post the issue in my help forum at http://www.excelguru.ca/forums. That way you're not restricted to just me seeing it? 🙂