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:
[xml]
[/xml]
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:
[xml]
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:
[xml]
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
End Sub[/vb]
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"
Call SomeRoutine
Case Is = "Button2"
Call SomeOtherRoutine
Case Is = "Button3"
Call ICantThinkOfAnotherName
End Select
End Sub[/vb]
15 thoughts on “Modifying the Ribbon – Part 1 of many”
Hi Ken,
You can setup Excel 2007 so that it shows you what is wrong with your Ribbon customisation code:
Click the Office button and select "Excel Options".
Click the Advanced tab
Find the "General" section and check the box next to "Show Add-in user interface errors".
Great tip, Jan Karel! I'll check that out. 🙂
Hello,
thanks for this interesting code. However, I have difficulties while using it in XLMA (add-ins) code : "Unabe to find the function CallAction in this workbook. Either the function doesn't exist, or the add-ins are disabled".
Any idea ?
Hi Aurelien,
Where did you put the code? It should be in a standard module, not a worksheet, class or ThisWorkbook module. I was able to copy/paste the first CallControl routine and the XML to a workbook, save it as an XLAM file, and it worked just fine.
Thanks for this very useful lesson, quite easier than MS's way!! I've just got a question : how do you find the list of all the imageMso? In your exemple, it's always the HappyFace. Is it possible to create some more pictures? Thanks for your answers, regards, Olivier
Hi Olivier,
I'm glad you found it useful. 🙂
With regards to finding which Mso images to use, you can check out the following links.
The hard way, but requires no additional software downloads: https://excelguru.ca/2006/11/26/modifying-the-ribbon-part-3/
A bit easier, but requiring a download from Microsoft, is to get their Excel 2007 icon gallery. You can download that here: http://www.microsoft.com/downloads/details.aspx?familyid=12B99325-93E8-4ED4-8385-74D0F7661318&displaylang=en
And if you want to make that a little easier to use, read my blog post on it here: https://excelguru.ca/2007/02/22/office-2007-icon-gallery/
Hope this helps,
Ken
Thanx for this. Another tip is that if you put the file in the directory C:\Documents and Settings\USERNAME\Application Data\Microsoft\AddIns
Then open Excel, click on the Office logo
Click on Excel Options
Click on Add-ins
Click on Go
The file name should be there again. If you check it, the ribbon will appear on any file
Hi Peter,
Interesting concept, that. Honestly, though, if I'm going to the trouble of saving the file in the addins folder, I'd rather save it as an addin file type as well.
Hi Ken,
One thing I'm struggling with. I have a status label in a custom group on the ribbon with^getLabel callback defined. Elsewhere in the code I have a slow macro. I would like to be able to invalidate the label control twice, first at the start of the macro to get "Processing" and once at the end of the macro to get "Done". However it looks like both fire on macro exit so I never see the "Processing" bit. Am I missing something really basic here?
Thanks for your help
Hi David,
Are you trying to put both label status messages from the getLabel callback? I would think you'd probably want to set things up like this:
Dim State as long '<--Module level variable Sub Button_Click Set State = 1 Invalidate the Label -->Run Slow Macro
Set State = 2
Invalidate the Label
End Button_Click
I'd then set the label's text based on the State variable.
Now, I haven't tried this myself, so it may not work, but that's the route I'd start out with.
Hope this helps,
Ken
Hi Ken,
Thanks for the rapid reply. The above is what I have tried. When the Button_Click action terminates the label gets updated by 2, but I never see the 1. I've played around with some other ideas but no joy so far.
David
This seems to work (I tried it with a zero delay in OnTime) but that gave the same results)
Private nState As Long
Public mgrxIRibbonUI As IRibbonUI
Public Sub rxLoadCustom1(ribbon As IRibbonUI)
Set mgrxIRibbonUI = ribbon
End Sub
Public Sub Define(control As IRibbonControl, ByRef returnedVal)
Select Case nState
Case 1: returnedVal = "Starting Macro"
Case 2: returnedVal = "Macro Finished"
End Select
End Sub
Public Sub MyMacro()
Call StartLabel
Application.OnTime Now + TimeSerial(0, 0, 1), "DoStuff"
End Sub
Private Sub StartLabel()
nState = 1
mgrxIRibbonUI.Invalidate
End Sub
Private Sub DoStuff()
Dim i As Long
Dim j As Long
For i = 1 To 10000
For j = 1 To 60000
Next j
Next i
nState = 2
mgrxIRibbonUI.Invalidate
End Sub
Works like a charm. I guess I have some stuff to learn on the Excel call stack ...
Glad you understood that, on re-reading I see I could have given a few more details 🙂
BTW, this is a somewhat subtle way of telling the user something is happening. I am much more of an in-your-face type of developer (ask Ken :)), so wouldn't a progress bar be better?
I'll have a think about the progress bar idea. I must admit being more an algorithm guy than anything else, so I'm only just upgrading from buttons in worksheets ...
The origin of the label is that the slow macro computes a portfolio valuation and cab fail for a number of reasons. Once the macro exits the label indicates the number of errors and warnings, with a drop down just below giving the list of what went wrong. In those conditions having the label change to Waiting when you pushed the Compute button seemed natural so that the user wasn't left looking at an old message. I gray out the active sheet while the calculation is going on so that the user isn't tempted to read the output before it's there.
Thanks again, your and Ken's help was much appreciated !