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
'Placeholder for other macros
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:
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
(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:
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.
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):
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:
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.)
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.