Who Says The Ribbon Is Hard?




Introduction


I was recently chatting with a friend, and he was asking how you can have dynamic ribbon buttons, buttons that are available depending upon worksheet events. I knocked up a simple example, and I thought I would share it here for anyone else who might be interested. It takes a few steps, but it is remarkably easy.


The example has three buttons within a single group, on a custom tab. The first button can be hidden by changing a cell value (a data validation cell in this case), or have its visibility restored. The second does nothing, whilst the third can change the image when a certain cell is selected, from one image to another. This principle could easily be extended to have say different groups of buttons for each sheet in a workbook, hide/expose the group upon activation/deactivation of the sheet.


CustomUI Editor


If you want to look at the xml, you should download the CustomUI Editor. It is hardly what you would call a sophisticated tool, but it does enable you to create xml for the ribbon customisations.


Coding Techniques


A couple of things about my code. I do not hardcode any values in the xml, apart from the ids of the code. Instead I use callbacks for all of the properties. As an example, I could setup a button in the xml like so


<button id =”btnDynaRibbon1″
        label =”Button 1″
        onAction =”rxOnAction”
        image =”ImportExcel”
        size =”1″
        visible =TRUE
        screentip =”Button that toggles the image” />>


which has all of the properties, except onAction, hard-coded.


I could do it this way, but I don’t, instead my xml looks like this


<button id =”btnDynaRibbon1″
        getLabel =”rxGetLabel”
        onAction =”rxOnAction”
        getImage =”rxGetImage”
        getSize =”rxGetImageSize”
        getVisible =”getVisible”
        getScreentip =”rxGetScreentip” >


where all of the properties are using callbacks, which means that the value gets set within the VBA code. This is the code for the rxGetLabel callback


Ribbon Code


Public Function rxGetLabel(control As IRibbonControl, ByRef label)

   Select Case control.Id

       Case CONTROLID_TAB_DYNA:                 label = LABEL_TAB_DYNA
       Case CONTROLID_GRP_DYNA:                 label = LABEL_GRP_DYNA
       Case CONTROLID_BTN_DYNA_1:             label = LABEL_BTN_DYNA_1
       Case CONTROLID_BTN_DYNA_2:             label = LABEL_BTN_DYNA_2
       Case CONTROLID_BTN_DYNA_3:             label = LABEL_BTN_DYNA_3
   End Select
End Function


It simply tests for the control’s id, and returns the caption for the label appropriately. All of the values tested for and the values returned are stored as constants in the VBA (they could just as easily be stored in a table on a worksheet). The values of all of those constants in the above code snippet are


‘ CONTROLID_ constant values
Global Const CONTROLID_TAB_DYNA As String = “tabDynaRibbon”
Global Const CONTROLID_GRP_DYNA As String = “grpDynaRibbon”
Global Const CONTROLID_BTN_DYNA_1 As String = “btnDynaRibbon1″
Global Const CONTROLID_BTN_DYNA_2 As String = “btnDynaRibbon2″
Global Const CONTROLID_BTN_DYNA_3 As String = “btnDynaRibbon3″


‘ LABEL_ constant values
Global Const LABEL_TAB_DYNA As String = “Dyna-Ribbon”
Global Const LABEL_GRP_DYNA As String = “Dynamic Ribbon”
Global Const LABEL_BTN_DYNA_1 As String = “Button 1″
Global Const LABEL_BTN_DYNA_2 As String = “Button 2″
Global Const LABEL_BTN_DYNA_3 As String = “Button 3″


Managing Changes


Taking one of the dynamic elements, the first button that can be made non-visible, the visible value is not a constant, but a variable as it has to be manipulated by the code


‘ VISIBLE constant values


Global VISIBLE_BTN_DYNA_1 As Boolean


This variable is initialised as TRUE in the Workbook_Open event, and in the callback procedure, the control’s visibility property is returned as whatever is in this variable


Public Function rxGetVisible(control As IRibbonControl, ByRef Visible)


   Select Casecontrol.Id


       Case CONTROLID_BTN_DYNA_1:             Visible = VISIBLE_BTN_DYNA_1


etc.


The visibility variable gets changed in simple worksheet event code (remember, I am using a data validation cell to drive this code).


Private Sub Worksheet_Change(ByVal Target As Range)


    Select Case Target.Address


        Case “$C$3″


            VISIBLE_BTN_DYNA_1 = Target.Value = “Visible”


    End Select


    mgrxIRibbonUI.Invalidate
End Sub


As you can see, it sets the variable depending upon the cell value. Finally, it invalidates the ribbon variable so as to rebuild our ribbon customisations. The ribbon variable gets set in the ribbon onLoad callback


Public Function rxDMRibbonOnLoad(ribbon As IRibbonUI)


    Set mgrxIRibbonUI = ribbon
End Function


Invalidating the ribbon variable causes the callback routines to be re-entered; this is how you change things dynamically.


Summary


Hopefully you can see how simple it is to create dynamic ribbon customisations, and how to manage the changes within your VBA code. You should also be able to see how this can be extended to other areas of dynamic interaction.


As I mentioned, I keep the CustomUI very simple, and rely on the VBA code to set all of the properties of my ribbon customisations. This is not necessary, it is a style aspect that I prefer, I like to the XML simple, reducing the amount of changes that I need to make there, and instead drive it all from VBA. I use a host of global constants for my ribbon values, but again as mentioned earlier, it would be just as simple (and maybe pro vide more manageability of the values) if all of the control properties were added as a table on a hidden worksheet, and grabbed from there on ribbon load.


My example workbook is attached .

16 thoughts on “Who Says The Ribbon Is Hard?”

  1. Idle speculation: how much easier this would have been/still is with classic command bars. At least in Excel 2010, if you add Custom Toolbars button to the QAT, you could still have classic simplicity, though not immediately available.

    A few years ago I decided form command buttons placed inside a drawn box/frame embedded in worksheets was a simpler approach. I have procedures which group the buttons and frams and copy or move to different worksheets and move within worksheets like classic floating command bars. Not as polished. This approach allows me to use the XLM command which hides the entire Excel UI. What I have is something which looks like Lotus 123’s idiot cousin, but it’s easier to use with VBA.

  2. Bob, nice to see a post! My MenuRighter addin has one two-state button that switches states based on whether it’s active, i.e., the form is showing. I thought I had figured out all the angles of keeping the button state synced with the form state and vice versa. But the other day I realized I could click the button and have it change state while in Edit mode. The userform then gets out of sync with the button. I don’t know if this happened with classic buttons as well. Any ideas?

  3. I looked at the Example Workbook but skydrive.live.com disables macros in the browser. And when I downloaded the workbook there were no macros to be found in the VB Editor. :(
    Microsoft fail.

  4. Thankyou so much for this straight-forward VBA solution! I easily added an rxGetEnabled function to enable/disable buttons based on the active sheet. All I need to figure out now is how to get my custom tab activated on opening the workbook (without needing a load of other code modules just for one function)

  5. Good coding, but it just goes to prove how rubbish the Ribbon interace is. Obviously Uncle Bill and his chums did no research into this when they decided to use the Ribbon-Disaster and their reluctance to go back to the simplicity of the CommandBar interface shows they still aren’t listening.
    When Uncle Bill gave the world Windows 3, he told us that once you’ve learnt one interface you’re half-way to learning all the others. The ribbon changed that but, Bill, it’s still not to late to admit your error and make the correction the world wants.

  6. For those looking for the download:
    Once on the web based excel page click on SkyDrive and download it from there by right clicking. No live ID is required this way.

  7. Hi Bob,

    Great article, that clarifies a lot of the Ribbon.
    I am also a Ribbon-hater though and my opinion has not completely changed yet.
    What I’m looking for is a template that can be reused, also by non-tech users. I used to have a settings sheet that would allow users to define their menu-commands. So it could add/delete menu’s just from code.
    My worry is that from now on I will need 2 tools to accomplish the same, the customUI editor and Excel.

    Or…is it possible to create/delete controls dynamically?

    Tips?

  8. Hi Jeroen,

    I am not a ribbon hater (although I am not a lover either :-)), but I do know it still creates polarised views. It is the Marmite of Excel (I love Marmite!).

    I am sure you could dynamically add controls if you used .Net to build the ribbon, but assuming VBA I don’t think you can.

    You could of course create extra controls and make them non-visible until you need them.

    Another alternative is to use a dynamic menu, you can add to those at runtime no problem.

  9. C9 = Cloud Nine?
    That is what I think right now.
    This trick finally solved my problem in getting the ribbon
    to behave the way I wanted using VBA.

    Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>