Finding Ribbon Images

Now that Excel 2003 is almost completely out of my life, it’s time to get my many add-ins ribbonized. Here’s what my UIHelpers ribbon looks like.

I used the Custom UI Editor and it was horrible. Did they put the VBE team on that project? I think I’ll find an alternative for the next add-in I convert. I don’t mind XML so much. The absolute worst part of creating a ribbon is finding a suitable button image. I downloaded Jim Rech’s add-in from Ron’s site. I scrolled through some button pages, but with 3,500 images, that wasn’t going to work.

Eventually, I changed the IsAddin property so I could see the list of image names. Then I filtered the names based on some keywords. Once I had a good candidate, I’d go back up to the ribbon, find that button, and look at it. This was much better than browsing through all of the pages hoping I’d find something suitable.

As you can imagine, I couldn’t leave well enough alone. I modified Jim’s code so I could filter the images based on their names directly on the ribbon. Jim had a nice system for what he wanted to do and I spent far too much time trying to shoehorn this new feature into that system. After a while, I started, more or less, from scratch. I should have done that from the start.

I started by adding a textbox to the ribbon where the filter term will go. It looks like this:

I don’t know what all that means, I just copied Jim’s and changed a few things. For the control handling part, I created two variables: mcolFiltered and mcolShown. mcolFiltered is a collection that holds all of the controls that match the filter. If there’s no filter, it simply holds all of the controls. When the ribbon loads, it sets up some module-level variables.

mlStart determines which member of the collection to show first. ResetListToUse gets the button names from a spreadsheet. msWhat is the filter term – set to nothing to start.

SetButtons handles what’s shown. First mcolFiltered is populated with all the control names that match the filter. Then mlStart is validated to make sure it’s within the range. Lastly, mcolShown is populated with which portion of mcolFiltered is on display. Most of the rest of the code just sets one or more module-level variable and calls SetButtons. Here’s some more code, just for fun.

Here’s the ribbon showing all of the controls with “filter” in their name.

As verbose as the image names are, they don’t contain enough information to do a proper search. Searching for “filter” works well enough, but if you want something related to money, you won’t find one search term that gets the job done. We need some meta data around each of the images. The first image is of a zero and is named “_0”. That image needs to turn up for searches such as zero, nil, number, digit, telephone, and probably some others. With 3,500 images, I’m not too interested in filling out all of that meta data. We need to crowd source it. Or hire some poor people.

Come to think of it, all those captchas that you have to type to prove you’re human are a lot of wasted effort. I should make a captcha system that shows a picture and asks the user to type two words that come to mind. I won’t actually check the words against anything, but I’ll use them to build my meta database for these images. Sure the captcha won’t work, but by the time people figure that out, I’ll already have my data. Brilliant.

You can download

10 thoughts on “Finding Ribbon Images

  1. I’ve recently spent hours and hours trolling through these. Real pain. Sometimes you find one and think ‘Maybe…if I can’t find anything better’, and keep scrolling without noting anything down. Once you inevitably DON’T find anything better, then scrolling through all those images again looking for the ‘Maybe’ one is like searching for a needle in a haystack filled with needles. I swear that one ‘Maybe’ was removed by gremlims, causing me to scroll through the whole lot many times before said gremlins put it back in again.

    Ha – captcha idea is funny. I think you’d get an over-representation of ‘WTF’ though.

    Aside: It’s about time that MS refreshed some of the more low-res images to higher-res.

  2. That’s nice Martin. I can’t figure out why our counts are different on the same filter. Also, it seems like if I choose large or small I get different results. I like it in a userform though.

  3. Guys – I’ve been thru this crazy image searching experience.
    it’s not that hard to make your own custom images and show them in the ribbon.
    Of course, you must get Steven Bullens most excellent graphic library so you can use the PNG file format.
    I purchased an icon editor, and used an image from it in the Ribbon.
    Then I made my own in Photoshop in the PNG format.
    Incredibly, the PNG image was clearer and more precise than the one created by the icon editor !

  4. I’ve ribbonized a couple of addins that other people use. It’s tedious, so for most addins I’m happy enough using old Excel 2003 menu-building code. I add a “Menu Commands” button to the Quick Access toolbar and just get to my addins from there. I actually kind of like having them all on one button. (I also use my MenuRighter tool to add my utility addin to several right-click menus).

  5. I have used Andy Pope’s Visual Ribbon Editor a couple of times and like it very much. You might give that a spin…

  6. There was a tool called ProcessTools that a company called ProcessIT made that did this for an Access database. Was pretty good but only shows like 100 images per page. But you could add tags to images and come back and search for the tags which was pretty sweet.

    yip still exists

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.