I believe that I've uncovered a bug in the implementation of the visible attribute of the separator element. Or rather, the fact that you cannot set the visible attribute to false.

The code I used to test this was as follows:

XML code for an Excel xlsm file:

Code in the Thisworkbook module:

Code in a standard module:

Upon saving all that and opening the file, you get a really ugly little group on the Home tab that looks like this:

Now, the interesting things here are that the first separator has it's visible property set to false. So why is it showing? The second separator is hooked up to the "Show Sep 2?" checkbox to make it dynamic. Uncheck it and the separator will stay there. I only hooked both up as I wanted to check if having the attribute set to static or dynamic would make a difference.

Finally, the Debug Mode is just there to allow you to see that the code does trigger and step through it while firing if you want to.  (It also logs the value of the property to the immediate window, but this saves you looking there.)  The entire Debug Mode and debug.print section could be dropped from the example, but then the checkbox doesn't appear to do anything. (As compared to doing something but being ineffective!)

I don't know how much this attribute would even be used, but the fact is that it doesn't work as advertised.  I'm going to log this one with Microsoft. 

A little background

A while ago, I blogged about the Excel 2007 Icon Gallery, which is available from Microsoft right here.  It's a useful tool when you're trying to identify the imageMso that you're after, no question... but there's a problem...  It's only available in Excel. 

Based on the information in my previous blog post, you'll know that I've also turned Microsoft's workbook into an add-in so that its always available to me.  I've made some fair use of it in playing around with Ribbon modifications as well.

Last week I was writing up some stuff with Word, and I found myself flipping back over to Excel to look up the imageMso that I needed.  This struck me as kind of silly, as I figured that it should be pretty easy to port this over to Word.  I mean, really, that was the whole point behind going to XML and consistent structure, right?  So I decided to do it.  In fact, I built a version for Word and Powerpoint.  Both they and the Excel version are included in the zip file attached to the bottom of this post.  I may also add an Access version later, but you'll have to hold on for that one.

Before I started on porting this to Word & Powerpoint though, I took on some comments from Zack Barresse (both from the aforementioned blog post feedback as well as a VBAX post) and decided to implement them as well.  I changed the userform a little bit and added Zack's ultimate lazy factor in; having the button automagically copy the imageMso name to the clipboard.  Just because I could, I also added the option to close upon copying, as I figured that someone may not always want to copy and exit.  The value of the checkbox is stored in the registry, so you only have to set it once.

Here's a picture of the new userform in action:

So then came the fun part...

Excel

Now, for me Excel was pretty easy as I've built more add-ins than I can remember.  In this case even easier, as I was only really adjusting work someone else already did.  If you've never installed an Excel add-in before though, you simply:

• Download the zip file and extract it to your computer somewhere. 
• I'd suggest copying the Office2007IconsGallery.xlam file from that location to store it in the addins directory which can be found at:
• Vista --> C:\Users\Username\AppData\Roaming\Microsoft\AddIns
• XP --> C:\Documents and Settings\Username\Application Data\Microsoft\AddIns
• Open Excel 2007
• From the Office menu, choose Excel Options|Add-Ins|Go...
• Check the box next to Office2007IconsGallery, then OK

Now, check the Developer tab for this:

No big secret here.  Just click a gallery, select an image, and you'll get the userform that I displayed above.

Word

Now Word was a little more entertaining than Excel, as I'd never built and addin for Word.

Here's the process I used to convert it:

• In Excel, I exported the userform and code module to the desktop
• I created a new Word .dotm (Macro enabled template) file, and imported the userform and code module
• After saving the file and closing both Word and Excel, I opened the Excel add-in in the CustomUI editor
• I copied all the XML code from the Excel add-in, and closed the file
• Finally, I opened up the Word dotm file in the CustomUI editor, pasted all the XML, saved it and closed it.

Honestly, that was the easy part.  As soon as I opened the file again, my group was there and it worked.  The problem now was deployment...

I'd just assumed that you could dump it in to Normal.dotm, and away you go.  While that works, it's a really bad idea, I found out, as the XML (at a minimum) goes with whatever documents that you create off of the normal template.  That's not really cool, as I didn't really want to send that modification to everyone, so I needed a different way.

Now, Word doesn't use Add-ins, per se, but rather a form called a Global Template.  While I'd heard of these, I'd never really built one in the past, as I've never created any Word utilities that I needed to share.  Basically, to make your template into a global template, you just drop it in Word's STARTUP directory... that's it.  In all honesty, this was a bit of a surprise, as if you do this in Excel, the file opens and is visible.  Word essentially treats all its templates stored there as add-ins, so there you go.

So to recap, if you want to use the Word version of the file:

• Download the zip file and extract it to your computer somewhere. 
• Copy the Office2007IconsGallery.dotm file to:
• Vista --> C:\Users\Username\AppData\Roaming\Microsoft\Word\STARTUP
• XP --> C:\Documents and Settings\Username\Application Data\Microsoft\Word\STARTUP
• Open Word, and it's already there on the Developer tab

There is no checking of boxes required to get this to load or anything.  It will just be there when you start Word up, and none of your new documents will inherit its XML code base. 

Powerpoint

Probably the most mystifying of the applications to work with in this regard is Powerpoint. 

Here's what I did first, much to my chagrin...

• In Excel, I exported the userform and code module to the desktop
• I opened Powerpoint, imported the userform and code module into the file and then saved it as a Macro Enabled Template (ppam) file
• After saving the file and closing both Powerpoint and Excel, I opened the Excel add-in in the CustomUI editor
• I copied all the XML code from the Excel add-in, and closed the file
• Finally, I opened up the Powerpoint ppam file in the CustomUI editor, pasted all the XML, saved it and closed it. 
• I opened Powerpoint and tried to install it as an add-in, only to be told it wasn't a valid file

Grr...

I double-clicked the file from Windows Explorer, and it opened up just fine with my customizations.  But when I closed Powerpoint and re-opened it, I could not get the add-in to load automatically.

It turns out that you need to add a little code to your add-in to get it to register itself for auto-loading.  So armed with this code, I opened up the file, jumped in to the VBE... and it was not there???

So a big note to everyone who is building their first add-in in Powerpoint... SAVE A BACKUP COPY TO A REGULAR FILE FIRST!  It looks like once you've converted to an add-in, that's it.  One shot, and you're done.  So back I went, repeating all the above steps, but this time with a regular Macro Enabled (pptm) file.  In addition, I also added the following code to get it to register for auto-loading:

So with the above code in the standard module with the RibbonX callback code, I saved the file (in the pptm format), then saved it as an add-in again.  Nothing really seems to happen at that point really except you get prompted for the directory to save it in, of course.  (Oh!  By the way, Powerpoint Add-ins go in the Add-ins directory.)  But once saved, you're back staring at your original file again, with no indication that the add-in ever really loaded.  At any rate, I then closed down Powerpoint, browsed to the add-in location in Windows Explorer, and launched it from there.  It works like a breeze now.

Fortunately for you, I've included the powerpoint add-in in the zip file as well.  To use it:

• Download the zip file and extract it to your computer somewhere. 
• Copy the Office2007IconsGallery.ppam file to:
• Vista --> C:\Users\Username\AppData\Roaming\Microsoft\AddIns
• XP --> C:\Documents and Settings\Username\Application Data\Microsoft\AddIns
• Double click the file in Windows Explorer, and it will open Powerpoint for you and register the file
• Browse to the Developer tab to see it waiting for you

After you've followed these steps once, you don't have to worry about lauching the file that way again.  It's registered to load automagically every time you launch Powerpoint.

Some conclusions

Whether you feel that Microsoft got it right with the Ribbon or not, I think you'd have to agree that deploying an identical UI enhancement across multiple applications would be complicated to say the least.  This was actually remarkably easy, in truth.  The biggest complication for me was learning the deployment side of the Add-ins/Global Templates.  Once done though, it was a piece of cake.  I'm actually fairly impressed. 

Downloading the Add-ins

Heard enough?  Sorry, just need to say a couple more things here...

• The original XML code in these files was all produced by Microsoft.  I have not modified it in any way at all.
• The original userform has been modified by myself to allow copying of the imageMso name, but this was originally Zack Barresse's idea.
• The superior laziness of being able to click a button to have the imageMso copied to the clipboard is definitely a Zack thing as well.  I wish I'd thought of how to be quite that lazy, but I didn't. 
• The idea to extend this to Word & Powerpoint, I'll lay claim to, though. 

And at long last... here's the file!

Office 2007 Icons Gallery

Hello folks,

Here's another resource that I'm going to add to my RibbonX table of contents... it's the list of Office 2007 Control ID's. This is a package of 24 Excel spreadsheets that list all of the Ribbon control names, types and even the tabs and groups that they reside in (if any). There's one file each for Excel, Access, Word, Powerpoint and the RTM updates, and the rest are all for Outlook.

If you are trying to figure out what a control's idMso is for Ribbon customization, these files will be invaluable to you. Just open them up, and start working through them with the filters to narrow down what tab your control is on, what group, etc... Very handy.

You may need to pass the Windows Genuine Advantage validation to get it, and be aware that it is an exe file that extracts into the goods. (Go figure that. You'd think they could just give you a zip file, but whatever.)

The file can be downloaded from this page on Microsoft's site.

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:

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:

(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.

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):

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:

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.

No more fooling around trying to figure out what the idMso is for the built in Excel commands when you're trying to customize the Ribbon. There is a great little download available from Microsoft to add a group of galleries to the Developer tab on the Excel ribbon. So what's in the galleries? Check this out:

And when you click on a button, you get a little expansion like below:

Not too shabby! Finally there is an easy way to figure out the idMso for the built in commands, and browse all the images in an easy to use location. (Okay, granted, I just found it, and I'm sure it's been up for a while. ) The really good thing here is that you can see ALL the images, not just the ones that are on the menus. Not to mention that this is actually efficient. You don't actually have to click on the item to get the id!
As an FYI, you'll need to pass the Genuine Validation tests to download the file, but so long as you can, it's all good. It comes as an .exe file (go figure), that extracts an .xlsm file. (Excel Macro enabled workbook.)

The one change I made to this, based on the recommendations of a friend, was to save the file as an Excel add-in (.xlam). Once done, I installed it as an Add-in (Office menu|Excel Options|Add-ins|Go). I now have it available whenever I need it.

EDIT February 23, 2007:

Based on Zack's feedback, I thought I'd make this change as well. I added a textbox to the userform, and modified the onAction code to the following:

The result of this, as Zack points out, allows us to easily copy the control name in case it's really long:

Another interesting thing here, though, is the code. Despite moving to the Ribbon for the Office 2007 UI, Microsoft still references the commandbars objects to get the images. I found that just a little surprising, as I'd assume that they would have abandoned it all together. Go figure!

Thanks for the ideas, Zack.

I found this kind of interesting, but somewhat strange...

A couple of nights ago I spent a significant amount of wasted time trying to get a callback to work in Visual Studio 2005. The problem was that I was trying to adapt my Dynamic Menu example to VB.Net, and the callback signatures for VB.Net and VBA are different. That's not really a big secret, and most of the callback signatures (in VB.Net, VBA, C# and C++) can be found in MSDN's Customizing the Office (2007) Ribbon User Interface for Developers (Part 3 of 3) article. Most, but not all currently.

To my chagrin, there was at least one callback signature missing... the getContent callback. For populating the dynamicMenu control, this is essential. That omission has been brought to Microsoft's attention, and hopefully the article will be updated soon.

Now, what's interesting about all of this... here's the signatures:

I'm fairly comfortable programming in VBA, but to me the VB.Net callback is far more intuitive to what I would have expected. In truth, despite working with the VBA signature for a while, I still scratched my head on why I was setting the value of "returnedVal" to a parameter. I knew that it was working, but was not sure why it was not looking like the VB.Net version, and why it was working.

The fact that it works makes sense, though, once you realize what is happening. I should have seen this way earlier, but I've never passed parameters like this. For my own purposes, I think I would have realized what was going on if the VBA version was written as:

Do you see it? I added the ByVal keyword to the first control. To me, this would have probably alerted me to consider this... It's the ByRef keyword that does it.

By default, all objects are passed to and from subroutines and functions ByVal, and the keyword is therefore usually omitted. The key difference between ByVal and ByRef, though, is that when you pass a parameter ByVal, a copy of the object is passed, not the original object. This means that anything you do to the object is done on the copy only, and when the procedure or function ends, the temporary object goes out of scope and the changes are lost. By contrast, when you pass a parameter ByRef, it passes the original variable. Any changes made to that variable are done on the original, and this is how the callback works... changes are made to the original "returnedVal" object that was passed as a parameter ByRef to the subroutine.

Personally, I don't really follow why the VBA callbacks would have been set up as subroutines with the "returnedVal" object passed ByRef. To me this seems like the perfect use for a function, which is obviously the route that Microsoft decided to take with VB.Net.

Why the difference? Is there a good reason why they would choose to go one route with VBA vs the other with VB.Net?

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

In this blogpost, I thought it might be nice to try a real example of something that someone may actually want to to. This example adds a menu to my XLG group, and populates it with all Excel files in a directory specified in a worksheet cell. Clicking the menu item will launch the file, and an additional button control will rebuild the menu if the file path in the worksheet cell is changed. One thing to be aware of, since this is an example only, is that no checking of the directory is done.

This example differs from the others I've blogged about, as it makes a truly dynamic menu. The name of the directory can be changed, and the number of files will vary from directory to directory. This creates a challenge, as we need to feed back XML to the Ribbon on demand, as well as record the details of each of the files since we have no collection to access later. I'm going to make an attempt to explain it all, so hopefully I hit the mark with it.

Base XML Required

The first thing that we need to do is add some XML to our file in order to set up a dynamic menu. This XML will start with an onLoad statement to capture the RibbonUI object (to allow menu updates), and also sets up the correct XML tags for the dynamic menu:

Notice that the rest of the details, namely the tab and group information is still the same as in past posts. We'll still have an "XLG" tab with a "TestTab" group on that tab. In addition, we'll have a menu item on that tab called "My Menu"

Capturing the RibbonUI object when the Workbook is opened
The first thing I do now is add a custom property to the ThisWorkbook module, allowing us to capture the RibbonUI object. Capturing this object will allow us to "invalidate" the RibbonX code, forcing it to rebuild the menu when we ask it to. The code for this (to go in the ThisWorkbook module) is:

Next, we need to add the routine we specified in the XML onLoad statement. It's this code that will actually be triggered when the workbook loads, and passes the RibbonUI object to our property for storage. This routine looks as follows, and goes in a Standard module:

How do we hold our file paths?
What we're going to do in this project is identify all the Excel files in a directory, and add the name of each one to a menu item. In order to open the files later, though, we'll need to be able to call up the file path somehow. In the old commandbar objects, we we could assign that path to the Tag property, or even the ToolTipText property, then retrieve it from there. Not so with the new UI, unfortunately. There is no collection to reference, and all you can get is the ID of the button clicked.

So how do we do it? I can think of two ways.

The first would be to build an index in a worksheet with the primary key (if I can call it that) being the control's ID. You'd then leverage a lookup to get back what you need. It would work, but I'd rather avoid using the worksheet like this if I could. (Just personal preference.)

The method that I've elected to use is a little more complicated, but keeps everything in code and out of the users' way. It involves creating a custom collection in order to store our items for use later. This rest of the article will be based on using this method.

To set up our Collection, we first need to create a new class module called "clsFilePaths", to which we'll add the following code:

(Believe it or not, that's it for the Class Module. )

To complete the setup for our Collection, we then go back to our Standard module, and insert the following line at the top of the module (after any Option lines):

Populating the Dynamic Menu

Now it's time to focus on adding the dynamic content to the menu. In our XML, we specified a routine called "GetContent" to populate the dynamic menu. We've got our Collection set up and ready, so we need to work out how to do this. Our complication is that we need to feed the XML back to the Ribbon to be executed. What I've done to deal with this is:

• Made the GetContent routine start the process of building the XML
• It then looks in the directory and figures out what files need to be added
• For each file it sends the details to a custom function to create the required XML
• That XML is added to the XML string
• The details of the file are then added to the Collection
• Once all files have been dealt with the XML is then passed back to the Ribbon for inclusion

Please note that each button is set up to call the btnCentral macro when clicked. This central macro handler will be important later.

The idea behind the AddButtonXML routine is that it will eventually hold all possible parameters to return dynamic vs static content to return the string. For now, however, I've only dealt with the tags that I need for the example. (To be clear, I'm not using the dynamic portion within, but felt while I was working on each tag that I might as well give all options possible for them.) I tried to comment them appropriately so that you can follow what they do. Both of the routines below go in the Standard module:

With all of the above code in place, your menu should now generate providing that you put a valid path, which holds at least one Excel file, in Cell B1.

Reacting to the Button Click
It's time to make sure that we can open our file. Remember that we used a collection to capture the file path earlier? Well now the trick is to get that out.

Since you cannot use just numbers as your ID for the XML code, we need to convert the ID returned by clicking the button into the index of our collection. This is why we started our Button ID's at 1 and kept growing; it matches the index of the Collection exactly. We can get the ID when a button is clicked, so we just need to extract the number from it, and use it to query our collection. The code below is the central button handler, and does exactly that. It also goes in the standard module:

Updating the menu for a new directory

The final part of this article deals with updating to a new directory, and getting the new files to show on the Ribbon.

This portion is actually done by leveraging the Invalidate method of the RibbonUI object. Remember way back in the beginning how we captured the RibbonUI to a custom workbook property? This is the reason. The routine I use does two things:

1. Invalidates the RibbonX, forcing a rebuild of the dynamic control
2. Clears the collection in preparation for the new menu items

The code, once again to be placed in the Standard module, looks like this:

And finally, we need to give the user a way to trigger this once they put their file path in cell B1. While we could have gone more complicated, such as adding a button to the menu or group, I elected to go the simple route. I dropped a form control on the worksheet and linked it to the following macro:

Conclusion

Adding a dynamic menu is much different than it was in Excel version prior to 2007, since we have to create our own collection objects and properties to hold the information we need. With a little organization, however, it's not impossible.

I have prepared a copy of a workbook that includes all the code used above, as the post may be a bit choppy to follow. Please feel free to download it and comment away. It is a Zip file, as I can't upload xlsm files to my blog yet.
The only instructions for use are:

• Enable macros
• Change the path to a valid file path, which holds at least one excel file, in cell B1 (you can include or ignore the trailing \)
• Press the button to update
• Try the menu on the XLG Tab's "Test Tab" group

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

For this installment, I thought I would focus on exploring the button control in depth, explaining what each piece of XML does when building the ribbon control, and what types of values are permitted for each one. In a follow-up article, we'll discuss how to make these controls dynamic, but for now we'll just focus on a very static button in a custom group.

Beginning With Static Items

Whenever we are programming, it is helpful to understand what pieces of code are assigned to what properties of the object. The following is a list of all of the components that we can set for a button, and where they show up:

Base XML Markup

The framework for the XML Markup is fairly static, although we'll explore some minor modifications to it in the next section. For most purposes, however, it starts with a declaration of the schema, opening your Ribbon object, referring to (or creating) a tab, and referring to (or creating) a group. Within the group tag is where we will place all of our button (or other) controls. The XML below creates a new tab called "XLG" after the "View" tab, adds a group called "My Testing Tab" and puts the button there.

Rather than explain all the XML at this point, be aware that the code to create any control within the group should be surrounded by the open and close tags: "<" and "/>"

For our purposes, I have constructed the base XML we'll use, which should be added to an Excel file by using the CustomUI editor. I would suggest creating, saving and closing a new file for this (make it an xlsm file). Once done, open it in the CustomUI editor and paste in the following XML:

The above code will be used for all demonstrations in this article. Once saved, the following group will show on the XLG ribbon tab:

Reviewing the button code and properties

We're going to begin our dissection by looking at the line that begins with the "button id" portion of the XML.

Remember! All of the XML markup is case sensitive!

"id" Tags

You must have an ID tag associated with your control, as this is how the application keeps track of what controls have what properties. You won't see this on the ribbon, as it is a "behind the scenes" feature.

You may use any one of the following for your ID:

• id
• The XML form is id="name of id"
• The name of the id must be unique for each id added
• Allows creation of custom buttons and controls
• idMso
• The XML form is idMso="id of control"
• The name of the id must match a valid office control name
• Allows "repurposing" of a control (replacing aspects of an existing control with your own intentions
• idQ
• The XML form is idQ="id of control"
• Used for working within defined namespaces (and is out of the context of this article.)

For the purposes of this article, I have used the "id" tag, and assigned it an id of "button1": id="button1"

The "description" Tag

The description tag is not publicly shown on the ribbon, and is an optional control with no default value. In our case, I assigned a value to this property with the following piece of XML: description="my custom description"

The "enabled" Tag

The enabled tag specifies if the control is active or not. It is specified with the following XML: enabled="true"

A control specified with enabled = "false" will not trigger anything when clicked, and will be grayed out, as shown below:

This property is optional, and will revert to a default value of "true" if not specified.

"image" Tags

When inserting images, you have the choice of specifying one of the following:

• image
• The XML form is image="my image name"
• The image tag allows you to refer to your own images that have been included in the file package
• imageMso
• The XML form is imageMso="MS Office image id"
• The imageMSO tag allows you to specify Microsoft Office images to be used on the ribbon
• To get the image id, go into Office|Excel Options|Customize, and let your mouse hover over the names in the list. At the end you'll see the id within brackets, as shown below:

For the purposes of this example, I've specified to use the built in Printer icon, by supplying the following XML: imageMso="FilePrint"

The image tag is completely optional, and will not show an image if you ignore it. If you omitted any type of image tag, (leaving the rest of our XML as is,) you would end up with a button like this:

The "label" Tag

The Label tag is used to assign a caption to a button, and is an optional tag. If you leave it blank, you will see no caption with your button at all. In our case, I assigned a value to this property with the following piece of XML: label="my custom button label"

This piece of XML manifests itself on the ribbon as shown below:

The "keytip" Tag

The Keytip tag's purpose is to allow you to access your ribbon control via keystrokes. If you read the XML carefully, you'll see that I've assigned a keytip of "x" to the XLG tab, and the following portion assigns a keytip to the actual button: keytip="zcb".

If you press Alt+x, you'll see the keytip show up on the button as follows:

According to the FAQ's in Microsoft's Customizing the Office (2007) Ribbon User Interface for Developers (Part 3 of 3) article, they suggest that you start all keytips with the "Z" character. In my experience, however, leading with an uppercase Z, (followed by whatever characters,) will yield you a keytip of "Y".

The "onAction" Tag

onAction is the property that specifies what code routine is run when the button is clicked. You will need to program the appropriate code for the routine yourself, and specify the name of that routine here. As there are examples of how to deal with this aspect on this blog, I will not cover it in much more detail at this point, except to say that the routine I have chosen for the example is called "rbnButton_Click", and is specified with the XML of onAction="rbnButton_Click".

It should be noted that you can designate a single routine as a central handler for all of your buttons, and take appropriate action by testing the id, or you can program a separate routine for each individual button. One thing that should not be done, however, is to try and handle controls of different types through a single onAction handler. Many controls require slightly different signatures, so they could fail if you try to go that route.

The signature for a button's onAction control in VBA is as follows:

I would also suggest that it is a "Best Practice" to ensure that all onAction routines are specified as Private, and placed in a standard module.

The "showImage" Tag

This tag makes the image visible or hidden, and is specified as follows: showImage="true".

The showImage tag can accept values of "true" or "false" only, and will default to true if not specified.

Interestingly, and I'm not sure if this is a bug or not, my tests show that this setting only seems to apply to a button if the size is set to "normal". I.e. "Large" images will show even if the setting is specified as false. What it should look like is as follows:

The picture above was generated by not specifying an image or imageMso tag at all. So if you really want a large picture-less button, it looks like that is the only route for now.

For reference, the showImage tag does work correctly with a "normal" sized button, and the picture below illustrates that:

The "showLabel" Tag

This tag shows or hides the button label, and uses the following XML to do so: showLabel="true"

ShowLabel is an optional tag as well, with a default value of true.

Strangely, this tag shows the same idiosyncrasies as the showImage tag above. Specifying false in our XML should yield the button below, but for some reason this picture had to be generated by removing the label="my custom button label" code from the XML.

Again, for demonstration, the following would display (correctly) when setting the showLabel tag to "false" and the size to "normal":

The "size" Tag

The purpose of this tag is easy to guess... it sets the size of the button image. In the examples above we've been using a large button, which was declared with the following XML: size="large"

The size tag is optional, and can accept values of either "normal" (small), or "large". If it is not specified, it will default to "normal"

A picture of our button using a size of "normal" is shown below:

The "screentip" tag

This tag allows you to set a screentip for your button by specifying the following XML: screentip="my custom screentip"

Again, this is an optional tag that will leave the field blank if not specified. A picture showing where this information is displayed is shown here:

The "supertip" tag

This tag allows you to set a supertip (similar to a screentip) for your button by specifying the following XML: supertip="my custom supertip"

Again, this is an optional tag that will leave the field blank if not specified. The following picture shows where the supertip information is displayed:

The "visible" tag

This tag sets the visibility of a button, and is specified as follows: visible="true"

It is an optional tag which can accept the values of "true" or "false", and will default to true if not specified.

As you might expect, setting the value of your control to false will hide it completely, as shown below:

Conclusion

I hope that this has helped to explain the components of a RibbonX button and the code used to create it. I'm also hoping that the pictures to show the effects of changes were useful to display exactly what effect modifying pieces of the XML would do to the overall output.

While it is necessary to understand the basic construction of a static button, it is unlikely that most people will have their needs met by one. To that end, there will be a follow-up post to this, explaining how to make these pieces dynamic, allowing us to change them on the fly.

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

The purpose of this post is to demonstrate how to use the Ribbon to interact with our application. Specifically, this example has an edit box which will update with the worksheet name every time you change worksheets. In addition, if you change the name in the edit button, then press the "commit" button, it will change the name of the worksheet to your new selection. (Providing the new name is allowed, of course.)

XML Markup Required

Our XML for this example uses the onLoad assignment to capture our ribbonUI object, allowing us to force a rebuild of our ribbon items when needed, as well as the onAction, onChange and getText callbacks.

VBA Code Required - ThisWorkbook Module

Again, we have the need to create workbook properties to set and retrieve the values of our ribbon controls. This time, we need to store the name in the editBox, which can be applied as a worksheet name, as well as the RibbonUI object. The editBox property is first used immediately upon opening the workbook, when we assign the active worksheet's name to it. It is then subsequently updated every time a new sheet is activated in the workbook. The code required in the ThisWorkbook module to accomplish this is shown below:

VBA Code Required - Standard Module

The following code contains all the callbacks necessary to:

• Store the ribbonUI object to the Workbook property (via the onLoad routine)
• Return the text for the editBox control when the RibbonX code requires it (rbnGetText routine)
• React to manual changes of the text values in the EditBox control (via the rbnEditBox1_Change routine)
• Respond to the button click requesting a worksheet name change (via the rbnButton_Click routine)

End Result

Once you've saved all the code, close and re-open the workbook. You'll have a custom XLG tab (that you've become used to if you're following the series.) On this tab you'll find a group that looks like this:

The code fires in this order when the workbook is opened up (assuming Macros are enabled):

• The RibbonX code is loaded, and the XLG tab is added
• The Workbook_Open event is fired, setting the EditBox1Text property to the name of the active sheet in the workbook
• The OnLoad procedure runs, capturing the RibbonUI object to a property for later use in triggering callbacks

At this point, you have the XLG tab showing on the Ribbon. When you click that tab, the "GetText" callback for the editBox is executed. This queries the property we just set, and adjusts the text in the editBox to the name of the active worksheet (which we just stored in the EditBox1Text property, as explained above. Because the button's properties are set in the XML, no additional callbacks are required to build the button.
Our Ribbon is now fully loaded, the editbox has a name in it, and the button is ready to be clicked. First though, try navigating to other sheets in the workbook. Every time you activate another worksheet in the workbook, you will trigger the following chain of events:

• The Workbook_SheetActivate routine is fired as the new worksheet is selected
• The name of the new sheet is sent to the EditBox1Text property and stored for later use (in the "sEditBox1Text" private variable)
• The editBox is invalidated, forcing the RibbonX code to be rebuilt
• During the rebuilding, the rbnGetText routine is called, which subsequently retrieves the worksheet name from the EditBox1Text property and builds it into the refreshed ribbon control

The only thing that looks any different now is that the sheet name will have changed in the edit box. You can click back and forth between sheets to watch it change, although that will probably get stale rather quickly.
So now, try typing something in the editbox and clicking the "Commit Name" button. If you check the active worksheet's tab, you'll see that the name has been updated. This was accomplished by the following actions, triggered when you clicked the button:

• As control passes from the editBox field to the button, the rbnEditBox1_Change routine is fired, which sends the value of the editBox to the EditBox1Text property for later use
• Once the above routine is complete, the rbnButton_Click routine is fired
• Control id is checked to make sure that we are reacting to the correct button. (Not really needed here, but would be if we added another button which used the same onAction routine.)
• The editBox's value is assigned to a string for checking
• The string is evaluated to make sure it is not blank. If it is, we tell the user and exit the routine without changing the name
• The string is checked to make sure that it doesn't conflict with an existing sheet name. If it is, the user is informed and we exit the routine without making changes
• If the string passed the above tests, we change the worksheet name.

While I can't really think of a good reason that you would want to add this functionality to the ribbon, I hope that it does a decent job of explaining the "how" of ribbon control and application interaction.

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

In this article I'm going to demonstrate using an "edit box" to change the description of a button on the Ribbon.

XML Markup

Unlike in prior examples, the XML Markup we'll use here uses a callback to assign the button name, which allows it to be dynamic:

VBA Code Required - ThisWorkbook Module

Like the checkbox example in part 7, we'll need to add properties to the workbook. In this case, however, it will allow us to capture the name of the button, and also the RibbonUI object. Next, we'll need to ensure that the button is assigned a default name when we start the workbook. The code to accomplish this is shown below.

VBA Code Required - Standard Module

Like in part 7, I again chose to assign the button and editbox name to constants. The remaining code required will:

• Use the OnLoad routine (with the same name as specified in our XML) to capture the RibbonUI object to a workbook property
• Set up the GetLabel callback which will return the name of our button
• Set up routines (again as specified in our XML) to react to the changing of the editbox value and the button clicks.

The End Result

Once you've saved all the code, close and re-open the workbook. You'll have a custom XLG tab (that you've become used to if you're following the series,) which has a button bearing the description "Set on Open", and an editBox showing "New Button Name:"
The code fires in this order when the workbook is opened up (assuming Macros are enabled):

• The RibbonX code is loaded, and the XLG tab is added
• The Workbook_Open event is fired, setting the values of the button name to "Set on Open"
• The OnLoad procedure runs, capturing the RibbonUI object to a property for later use in triggering callbacks

At this point, you have the XLG tab showing on the Ribbon. When you click that tab, the "GetLabel" callbacks for the button is executed. This queries the property we just set, and adjusts the button name to that value.
Our Ribbon is now fully loaded, the editbox is clear, and the button bears its default caption. Typing something in the editbox and hitting enter will set off the following chain of events:

• The EditBox1_Change routine is fired
• The name in the editbox is assigned to the button name property
• The button control is invalidated, forcing the RibbonX code to be rebuilt by calling the GetLabel routine

The end result is that the value in the editBox is sent back to the button, and the name of the button changes to what you just submitted.