Ribbon, Step-by-Step. Part 2 (Begin Customisation)

In the last part of this series, we went through the different file formats as a precursor to starting Ribbon customisation. You should by now have downloaded the Office 2007 Custom UI Editor, as this is the tool we will be using to customise our RibbonX and generate callbacks for VBA. We'll start with a run down of the tool itself. Remember, you need to have the .Net 2.0 framework, or above loaded on your development machine.

The Office 2007 Custom UI Editor

Without worrying too much about the code currently in the RibbonX pane (It is much easier to read when made wider, but the confines of this blog needs it narrower and causes the code to wrap), let's just familiarise ourselves with the various parts of the tool. This shouldn't take long as the interface is basic and un-cluttered with just the very necessary of options. Of note is the image store, this is where the collateral for new controls, like the 'gallery' are stored for placing in the correct place in the file. The toolbar is pretty self-explanatory, except maybe the 'Validate' button, which goes off to the framework and validates your RibbonX code for valid members and attributes and that it is well 'formed'.

Then there's the 'Generate Callback' button, which scans your code for 'action' attributes, like getImage, and generates code stubs for them all. More on that later, but below is the 'Callback' tab that is generated by pushing this button and the code 'stubs' that can just be cut and pasted.

Lastly, before we progress with our first code, I want to draw attention to the 'Sample' menu option (see below). When you install the tool a few are there, but by navigating to the 'Samples' folder in the installation directory, you can place XML files which will appear as sample in the list. (On my machine this is C:\Program Files\CustomUIEditor\Samples), a handy feature to store your frequently used RibbonX.

Components of the Ribbon

Below is a (rather squat) version of the ribbon, demonstrating the components we will be working with.

Ribbon

Although not detailed on the above image the ribbon is the entire area, containing Tabs, Groups, controls, etc. When we come to our first coding, you will see that this member sits right at the top of the hierarchy.

Tab

The tab is the way of navigating between different 'topics' on the ribbon, Home, Insert, Page Layout, etc.

Groups

Groups sit on tabs and contain other controls, allowing you to 'group' like controls, as such, the 'Group has no attributes, just children like buttons, boxes, etc.

Control(s)

Contained on Groups or Menus (Office Menu), common types are button, gallery, dropDown, splitButton, etc

Tab Set

This is a container that holds contextual tabs, This groups the contextual tabs that are shown when the focus (cursor) has a certain selection, such as in this instance, when a pivot table is selected. This is without doubt a step forward in the ribbon, hiding the features only until the user needs it. (Excel 2003 has about 39 toolbars most of which users never see)

Office Menu (see below)

This is similar in functionality to the old 'File' menu and contains, New, Save, Save as..., Print, Excel Options (the old Tools>Options... menu choice) and the Most Recently Used (MRU) list, (excuse mine!)

Dialog Launcher

This little button, which is actually just a container and needs a button child to action it, will fire the 'old style' dialog or your custom UserForms, MsgBoxs, etc., so if we look at the dialog launcher highlighted in the Ribbon image above, it will launch the dialog currently accessed through Format>Cells, focused on the 'font' tab.

Lastly, the interesting part is while Microsoft dumped 'CommandBars' for the Ribbon, the old StatusBar, which has now got much more functionality, is now...a CommandBar! (CommandBars("Status Bar")).

Our First Customisation

startFromScratch

The most basic thing you can do is to remove the standard ribbon, almost entirely. This will not be necessary in all code situations as you will be sometimes just adding to existing tabs, etc. You should know however that startFromScratch="true" is necessary if you want to author anything to do with the QAT. (Personally, I think the QAT should be left alone as it is very personal to the user).

For this first example we will do this in numbered steps so you can hopefully follow along.

IMPORTANT: All RibbonX members, etc are typed in camel-case. this says that they have no spaces, start with a lower-case letter and then have the first letter of each new word capitalised, so onAction, startFromScratch, insertAfterMso, etc. There are some names which defy this logic <group idMso="GroupFont"> (note the GroupFont capitalises both words), so you must be careful, but certainly try camelCase first.

NOTE: A big advantage of what we are about to do is that all the code to customise is contained in the workbook, close the workbook or unload the add-in and all your Ribbon will return exactly as was, you cannot damage anything, unlike sloppy coders who often left CommandBar litter after their code exited.

NOTE: Throughout all of this Ribbon development, particularly when we get on to files containing VBA code, I would set up a folder somewhere on your local machine and set it as a 'trusted location' under, Office Button>Excel Options>Trust Center>Trust Center Settings... This will prevent you having to enable macros each time you test your code. (Obviously, (apart from the 'Full' dictator app below, that has VBA code), you will not need that for any workbooks in this second part, as these are code-less demonstrations)

So...

1. Open a new Excel File and save it as test.xlsx.
2. Close this file (this step is a little annoying each time you want to test, but essential so that the Custom UI Editor can open it, without a share violation)
3. Open the Custom UI Editor
4. In the editor, press the open button and navigate to your new file. You should at this stage see nothing.
5. In the RibbonX pane, type the namespace identifier (this tells the code where in the framework to look for the 'hooks' and starts every piece of RibbonX) and a ribbon member, followed by the startFromScratch attribute, as below.
6. Press the 'Validate' button to check compliance
7. Press Save and Close
8. Now open your workbook!

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon startFromScratch="true">
    </ribbon>
</customUI>

If everything went to plan, you should have virtually no Ribbon left, not quite a dictator application, but equally not bad from one line of code. If you look carefully, you will find the Office Menu has shrunk to a few very basic features and that's about it. Compare the picture below with the one above.

If you still have Ribbon, then you have not got the RibbonX quite right and it is not very forgiving in this way. Generally it will be a case issue (camelCase), but it does not throw a syntax error, just doesn't display or change the Ribbon, so always check that if the unexpected happens.

As we move on, we will add other elements, etc between the start <ribbon> and end </ribbon> tags, which makes sense as everything is 'contained' in/by the ribbon.

Full 'Dictator' Application

Many of you will not call the code above a dictator application as there are still some elements of the Excel UI that can be 'played' with by users. I am grateful to Jim Rech for the lead in discovering how to do this and big surprise, the only way to do it is to return to pre-VBA days of Excel4 macros, which is totally amazing! If you paste the code below into a new workbook and run it, you will see an Excel 2007 full dictator application with VBA (running XL4 macros)

Sub RemoveAll()
'ExecuteExcel4Macro portion by Jim Rech
'Other code Nick hodge
    With Application
        .ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
        .CommandBars("Status Bar").Visible = False
        .DisplayFormulaBar = False
        .DisplayScrollBars = False
        .Caption = "It's all gone!"
        .ActiveWindow.DisplayHeadings = False
        .ActiveWindow.DisplayWorkbookTabs = False
    End With
End Sub

Lastly for today, we will go back to RibbonX and remove the existing Ribbon, create a custom tab, with a built in group, that just works, no code and saved as an xlsx

1. Open a new file and save off somewhere as an xlsx and close
2. Open the file in the Custom UI Editor
3. Enter the code below into the RibbonX pane
4. Validate the code
5. Close the file and the Custom UI Editor
6. Open the workbook and it should now have one tab, called favourites and a group (GroupFont) that if you try the controls on it, will just work.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon startFromScratch="true">
    <tabs>
      <tab id="myTab" label="Favourites">
        <group idMso="GroupFont">
        </group>
      </tab>
    </tabs>
    </ribbon>
</customUI>

Hopefully this is still fairly simple, note that you have a ,<tabs></tabs> tag as a ribbon can have multiple tabs and then below that is a <tab></tab> tag. This sets our single tab (remember we removed the existing with the startFromScratch attribute), between these tags, as they are 'contained' on the tab, we use the <group></group> tags to bring in Microsoft's 'Font' group with all it's standard controls, that just work!

The only attribute a member needs is either an id as in the tab that uniquely identifies it, or an idMso, as in the group, which identifies a built-in tab, group, control, etc. basically, if Microsoft made it, it's idMso, if you do, it's just id.

So that's enough for today, but you just removed the existing ribbon, replaced it with a custom tab, called favourites, placed on it a working 'copy' of the font group, all with 10 lines of code. (and that's generous as I have to have opening and closing tags, so it's really five!)

More to come... Stay tuned for part 3!