Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

How to make my add-in work as a ribbon

Latest post Wed, Jan 16 2008 1:09 PM by zfraile. 15 replies.
  • Mon, Jan 14 2008 11:29 AM

    • zfraile
    • Top 25 Contributor
    • Joined on Wed, Jan 9 2008
    • Posts 41
    • Points 861

    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?

    • Post Points: 37
  • Mon, Jan 14 2008 12:07 PM In reply to

    Re: How to make my add-in work as a ribbon

    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.

     

     

     

     

     

     

     

    Mike Alexander Microsoft Excel MVP www.datapigtechnologies.com
    • Post Points: 21
  • Mon, Jan 14 2008 12:50 PM In reply to

    • XL-Dennis
    • Top 25 Contributor
    • Joined on Wed, Jan 9 2008
    • Östersund, Sweden
    • Posts 39
    • Points 671

    Re: How to make my add-in work as a ribbon

    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.

    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | MVP | 2nd PED

    • Post Points: 21
  • Mon, Jan 14 2008 1:17 PM In reply to

    • zfraile
    • Top 25 Contributor
    • Joined on Wed, Jan 9 2008
    • Posts 41
    • Points 861

    Re: How to make my add-in work as a ribbon

    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.

    • Post Points: 5
  • Mon, Jan 14 2008 5:04 PM In reply to

    • zfraile
    • Top 25 Contributor
    • Joined on Wed, Jan 9 2008
    • Posts 41
    • Points 861

    Re: How to make my add-in work as a ribbon

    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>

    Filed under: ,
    • Post Points: 21
  • Mon, Jan 14 2008 10:36 PM In reply to

    Re: How to make my add-in work as a ribbon

    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.

     

     

     

    Mike Alexander Microsoft Excel MVP www.datapigtechnologies.com
    • Post Points: 21
  • Tue, Jan 15 2008 1:46 AM In reply to

    • Nick Hodge
    • Top 10 Contributor
    • Joined on Sun, Dec 23 2007
    • Southampton
    • Posts 543
    • Points 9,431
    • MVP

    Re: How to make my add-in work as a ribbon

    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)

    Regards
    Nick Hodge
    Microsoft MVP, Excel
    Southampton, UK

    Filed under: , ,
    • Post Points: 21
  • Tue, Jan 15 2008 4:24 AM In reply to

    • Andy Pope
    • Top 75 Contributor
    • Joined on Wed, Jan 9 2008
    • Posts 15
    • Points 351
    • MVP

    Re: How to make my add-in work as a ribbon

    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" /> 

      

     

    • Post Points: 21
  • Tue, Jan 15 2008 9:12 AM In reply to

    • zfraile
    • Top 25 Contributor
    • Joined on Wed, Jan 9 2008
    • Posts 41
    • Points 861

    Re: How to make my add-in work as a ribbon

    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. 

     

    • Post Points: 37
  • Tue, Jan 15 2008 9:43 AM In reply to

    • XL-Dennis
    • Top 25 Contributor
    • Joined on Wed, Jan 9 2008
    • Östersund, Sweden
    • Posts 39
    • Points 671

    Re: How to make my add-in work as a ribbon

    Zack,

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

    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | MVP | 2nd PED

    • Post Points: 5
  • Tue, Jan 15 2008 10:06 AM In reply to

    • Andy Pope
    • Top 75 Contributor
    • Joined on Wed, Jan 9 2008
    • Posts 15
    • Points 351
    • MVP

    Re: How to make my add-in work as a ribbon

    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" />

     

    • Post Points: 21
  • Tue, Jan 15 2008 2:39 PM In reply to

    • zfraile
    • Top 25 Contributor
    • Joined on Wed, Jan 9 2008
    • Posts 41
    • Points 861

    Re: How to make my add-in work as a ribbon

    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?

    • Post Points: 21
  • Tue, Jan 15 2008 3:14 PM In reply to

    Re: How to make my add-in work as a ribbon

    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>

    Mike Alexander Microsoft Excel MVP www.datapigtechnologies.com
    • Post Points: 5
  • Wed, Jan 16 2008 12:45 PM In reply to

    Betreft: How to make my add-in work as a ribbon

    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

     The XML: <comboBox id="rxBookMarksDD" sizeString="MMMMMMMMMMMMMMMMMMMMMMMM" label="My Bookmarks" onChange="BookMarkClicked" getItemCount="BookMarkCount" getItemID="BookMarkID" getItemLabel="BookMarkName" getItemScreentip="ScreenTip">

    </comboBox>

    Regards, Jan Karel Pieterse www.jkp-ads.com
    • Post Points: 5
  • Wed, Jan 16 2008 12:54 PM In reply to

    Betreft: How to make my add-in work as a ribbon

    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:

    <comboBox id="rxBookMarksDD" sizeString="MMMMMMMMMMMMMMMMMMMMMMMM" label="My Bookmarks" onChange="BookMarkClicked" getItemCount="BookMarkCount"></comboBox>

     

    Regards, Jan Karel Pieterse www.jkp-ads.com
    • Post Points: 21
Page 1 of 2 (16 items) 1 2 Next > | RSS
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.