How to make my add-in work as a ribbon

So we upgraded to 2007 at work.  I've been struggling with it at home for close to a year.  I've relearned some of the shortcuts which are pointlessly (if that's a word; if not, it should be, because how else do you describe the "upgrades" in Office '07?) different, but have not been able to get around to updating my tools add-in because it just didn't have the payoff for home-use only.  Well, no more, I can no longer sit back and hit Alt-X-Y (and why, Microsoft, why?  What is the reason for assigning a default "Y" and not letting me pick??  Was the "Y" the lucky target in a dart-throw?).  My tools are called Zach's Tools, dang it, and the shortcut should be a simple Z.  So, rant aside, I have managed to play around with the Data Pig template and the keyTip property to get my tools to use the Z.  Now instead of Alt-X-Y, it's Alt-Z-Z to get to my ugly old-school style menus.

My problem is this:  I want to setup a custom ribbon to take over the old menus I have.  That breaks down into two subproblems.  One is that there's about 40 different tools and I don't relish the thought of building the ribbon by hand.  I suppose I could use the Data Pig template, but is there a better way?  The other issue is that there just isn't room on the ribbon to fit them all and, even if I could, I don't know if 40 items on a ribbon is the best way to go.  Any thoughts?  The tools are broken down into 5 categories so I was thinking maybe a drop-down list for each of the categories, but I don't know how to do that.  An example of that would be the drop-down under the "From Other Sources" item on the "Data" ribbon.

 Also, one other problem is that the first category of my toolbar is a custom bookmark menu with my 20 favorite files.  I can change those files from a userform which saves the names and paths on the add-in worksheet.  Those are read by the add-in on load so the names of the toolbar items are not static.  I'm guessing this is a no-go with the ribbon layout.  I suppose I can just pin my favorites to the Home menu to get a similar effect, but I hate to lose the functionality if I don't have to.

 How has everyone else been dealing with the transition of their toolbars?

Hi Zach,

Excel MVP Ken Puls just came out with a book on RibbonX that looks very useful.  I haven't bought it yet, but it seems very thorough.  Enter "RibbonX: Customizing the Office 2007 Ribbon" into Amazon to find it. It looks like they walk through the steps to create multi-level menus that look much better than jamming 40 buttons on one tab.

The easy option is to use the DataPig Ribbon Customizer to create separate tabs (representing your 5 categories) with a few buttons on each tab.  However, it may not flow the way you want it to.

The dynamic "custom bookmark menu" is a bit trickier.  As far as I know, you can't dynamically change menu items for an add-in's RibbonX.  I may be wrong though.

Hi Zach,

I have updated som clients solutions to fit better with the Ribbon UI. At the same time I have also said to some clients that it's not necessary to update the solutions. Most solutions will work with the Ribbon UI and they are available under the tab "Add-in".

In Your case it seems to be a large customized solution. I would say it's a good opportunity for You to evaluate the tool and see if it's possible to break it down in more small tools by using several add-ins that are controled by one main add-in. Perhaps this will be a better approach to get a dynamic solution? What You can consider to customize the QAT (Quick Access Toolbar) with the most frequent used tools. In addition, as Mike also says, it's possible to create submenues. No matter what, there exist no 'quick and dirty' way to customize the tool.

I got my copy of Ken Puls's et al book today which I will review on my blog. However, I believe it will be a good investment to buy the book first and then approach the Ribbon customization of Your tool.

Thanks.

 I will look forward to that review, Dennis.  It sounds like as good a book as any to pick up at this point.

 Truth is, I was kind of hoping not so much for a quick-and-dirty solution as a quick-and-polished solution, thinking that after a good 18months of availability (including the beta) that someone would've whipped together a tool to generate the RibbonX, like the DataPig model on steriods.  As much as I enjoy learning new things, I've been burned too many times learning something new that ends up delivering little to no added value over just doing things the old way, and RibbonX seems to fall right into that bucket.

I should add that the QAT does seem like a good alternative, but is there any way to change the shortcut keys on those?  I guess not since it'd probably conflict with one of the ribbon shortcuts.

But enough complaining.  I have an 80,000 row file to work on...time to start enjoying the fruits of the upgrade and not just the pits.

Going with the menu-in-a-ribbon structure, my first task is to build up some menus.  I'm stumped here.  So far I have managed to create the ribbon, the menu, and the menu buttons.  So far so good.  However, I can get the keytips to work for the ribbon and for the menu, but they fail to appear on the menu buttons.  In my example below, I can hit Alt-Z, F to bring up the submenu.  I want to be able to hit B to launch my borders macro, but the little B in a box does not show up.  What am I missing?  I know it is possible because when I pull down the same menus from the Add-Ins ribbon, the keytips (actually the accelerator keys, defined with the & symbol) work fine.

 Please help. 

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" loadImage="LoadImage" >
   <ribbon startFromScratch="false">
      <tabs>
         <tab id="ZT" label="ZT" keytip="Z">
            <group id="Formatting" label="Main">
               <menu id="menu1"
                  label="Formatting"
            keytip="F"
                  itemSize="normal" >
                  <button id="button1"
                     description="button1"
                     label="Borders Top and Bottom"
               visible="true"
               keytip="B"
                     onAction="borders" />
                  <button id="button2"
                     description="button2"
                     label="Comma / No Decimal"
               keytip="C1"
                     onAction="CommaForma" />
                  <button id="button3"
                     description="button3"
                     label="Percentage"
               keytip="e"
                     onAction="PercentFormat" />
                </menu>
             </group>
          </tab>
      </tabs>
   </ribbon>
</customUI>

I can't seem to make it work either.  I even tried using my old ampersand trick  ( keytip = "&amp;B" ). Still no luck.

I think it may have something to do with the fact that the button are nested in a menu.  They're may be a way to do it, but I can't seem to find it. 

From what I understand, keytips are different from accelerator keys  so the fact that the tips show up in the old addin ribbon doesn't mean a whole lot. 

Maybe someone else can solve this mystery.

I suspect the same as Mike, it should work with a menu, but suspect you will need to use a box or similar to group those controls. Unfortunately a bit pressed at the minute, but if it's not fixed this evening, I'll take a shot (UK time)

Hi,

Preceeding the letter with the ampersand works. And to do that you can use &#38;

<button id="button1" description="button1" label="&#38;Borders Top and Bottom" visible="true" keytip="B" onAction="borders" /> 

Thanks, Andy, that works.  I did some more googling and came across a ribbon forum that had this little gem buried inside:

http://pschmid.net/office2007/forums/viewtopic.php?t=101

So I went back and tried Mike's solution of &amp; but applied it to the button label as you did, and that works as well.  You don't even need the keytip="B" because, as stated above, it's not really a keytip but an accelerator key, which is fine with me.  I hardly see the benefit to being able to use a keytip with a letter that's not in the label or that is more than one letter.

This whole thing has got me thinking since the solution seems like such a kludge.  If you look in the Home ribbon under the Format, Insert, or Delete menus, I'll bet those stock menus are coded using the same kludge-y solution.  I say this because if you look at any other item that has a keytip, you'll see that the letter corresponding to the keytip in the label (if there is one) is never underlined.  However, in these three menus, they are all underlined, and they all follow the rules of accelerator keys in that they are only one letter and the letter is somewhere in the label.

That also kind of irks me because the whole ribbon-is-more-user-friendly argument goes out the window if, basically, they've done nothing more than transplant some of the 2003 style menus to be menus in the ribbon.  It's the same old format but one click or keystroke further to find. 

Zack,

In addition to Patrick's forum You can also take part of Ken Puls blog, especially the Ribbon section.

Mike's suggestion works, only it needs to be applied to the label rather than the keytip text

<button id="button1" description="button1" label="&amp;Borders Top and Bottom" visible="true" keytip="B" onAction="borders" />

Thanks again.

 Maybe I'm getting greedy, but any idea how to do either of the following:

1.  use the < sign as an accelerator key?  I've successfully used the > sign as below

                     label="&amp;> Row Height"
but if I use

                     label="&amp;< Row Height"

the ribbon won't load.

 2.  Make the menu buttons big with descriptions as they are in the Windows - Freeze Panes menu?

Use these entity references in XML just as you do the ampersand

&lt;  for < (less than)

&gt; for > (greater than)

For the big menu buttons, try itemSize="large" as in this example taken from the Jensen Harris Blog

 <menu id="splitMenu" itemSize="large">
      <button id="b2" imageMso="HappyFace" label="Smile"
        onAction="smile" description="View pages about smiles."/>
 </menu>


On the bookmark menu:

I'd add a combobox control and the following VBA to fill it and use it:

Option Explicit

Dim moRibbon As IRibbonUI

Dim msRefTo As String

Dim moNm As Name

dim msBookMarks(0 to 3)  as string

Sub rxcustomUI_onLoad(ribbon As IRibbonUI)

    Set moRibbon = ribbon

    msBookMarks(0)=”Macro1”

    msBookMarks(1)=”Macro2”

    msBookMarks(2)=”Macro3”

    msBookMarks(3)=”Macro4”

End Sub

Sub BookmarkClicked(control As IRibbonControl, text As String)

            ‘Assuming we get a macro name

            Application.Run text

End Sub

'Callback for rxBookMarksDD getItemCount: Tells RibbonX how many items the combo will have to fetch

Sub BookMarkCount(control As IRibbonControl, ByRef returnedVal)

    returnedVal = UBound(msBookmarks)-LBound(msBookmarks)+1

End Sub

'Callback for rxBookMarksDD getItemLabel: Is called automatically by RibbonX as many times as set in "BookMarkCount" has

Sub BookMarkName(control As IRibbonControl, index As Integer, ByRef returnedVal)

    returnedVal = msBookMarks(index)

End Sub

</comboBox>

Being unable to edit my post (I end up in an endless loop which takes me back to the edit post window every time) I add the correct XML here: