I've decided to start playing around with the Ribbon, and figured that I might as well document my journey through this new piece of Excel. I'm going to start fairly slow with it, and add more as I experiment with different pieces.
For reference sake, the examples in this and following posts will be constructed running Office 2007 on Windows Vista, both of which are the RTM, not Beta versions. You will need Office 2007 to follow along, but you don't need to be running it on Vista, per se. My understanding is that it will help to be using the full version, but if all you have is access to the Beta2 or Beta2TR versions, then you should be okay for the most part.
It would also be a great help to have the CustomUI tool that you can download from OpenXML Developer. If you don't want to do things the easy way, you can always follow the hard route from Microsoft's article. 😉
Okay, so here we go...
Part 1 - The basic XML framework
The ribbon is constructed in XML, and we'll need a little XML to make our own. First though, we'll go into Excel and create a new workbook (or use the default blank one). Save it as an "Excel Macro Enabled Workbook" (xlsm), and close the file.
Now, open the Office 2007 CustomUI Editor that you downloaded above, and open up your Excel file. You'll notice that you are staring at a blank page. Paste the following code in there:
Now... this won't do anything for you, but basically it's the main framework that we'll adjust when we're playing with it. See the blank space in the middle? That's where we're going to put in the XML that we actually want to use to do stuff. They key is that any (and as many) tabs we want to create will go between the "tabs" and "/tabs" tags.
Part 2 - Add Some Useful XML
Now, we're going to drop in a little more XML to round things out. In that blank space, we'll add some code to create a new tab on the ribbon called "Custom Tab". Because it's boring to have a totally empty tab, we'll add a group to it, called "Custom Group". No point in leaving that empty either, so we'll put in 3 buttons. We'll call them something really imaginative, like "Custom Button 1"... you can probably guess the rest.
So here's the XML for that. Just drop it in the blank space above:
Now, you'll want to Validate the XML before you save it, to make sure it's well formed. If it isn't, nothing will happen in Excel. No errors, no modification, no nothing. (I actually find that quite irritating, in truth. I'd rather see something to alert me that there is an issue.) To validate the XML, just click the button with the red check mark on it. If it tells you the XML is well formed, all is good, so save the file. If not, try following the steps above to make sure it looks like this:
Part 3 - Link VBA to the Ribbon
So now we have our XML set and saved, we need to reopen the workbook in Excel. You should see a nice new tab on the menu called "Custom Tab" that looks like this when you click it:
Now this is great, but you get an error when you click the buttons, since there is no VBA associated to do anything. So press Alt+F11 to open the VBE. Insert a new standard module, and drop the following code in there:
[vb] Private Sub CallControl(Control As IRibbonControl)
MsgBox "You clicked " & Control.ID
Once you've done that, exit the VBE, and try clicking those buttons. It should tell you what button you clicked.
Actually, if you pay close attention, you'll notice that it feeds you the ButtonID that we set in the XML. (No space... The actual caption on the button has a space.) This is important to note, as it gives us a way to truly tell what button was clicked. The XML we wrote tells the button to call the CallControl subroutine, and passes it's ButtonID to that routine. So if we wanted to call a certain macro for each button, we could use code such as:
[vb]Private Sub CallControl(Control As IRibbonControl)
Select Case Control.ID
Case Is = "Button1"
Case Is = "Button2"
Case Is = "Button3"