Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.
Nick Hodge's Excel Blog » Ribbon, Step-by-Step. Part 4 (Exploring Other Controls)

Ribbon, Step-by-Step. Part 4 (Exploring Other Controls)

So we've explored the file formats, created the shell for a dictator application, made some basic ribbon, tab, group and control changes and customised the Office menu. We've re-purposed some Microsoft controls to our own use and now we will be running at pace through some of the other controls available to you. I doubt we will cover all of them, but you can download a bunch of my files here, which demonstrate more of what you are learning here and also contain a 'popular' control and attribute listing. So...let's get started.

The Core Of Our Model

As usual we will start with our basic building blocks of declaring a customUI and the ribbon itself, it's startFromScratch attribute and a custom tab, which we will place after the built-in add-ins tab, like so:

<customUI onLoad="loadRibbon" xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon startFromScratch="false">
    <tabs>
      <tab id="myTab1" label="Nick's Tab" insertAfterMso="TabAddIns" visible="true" keytip="Z">
    </tabs>
  </ribbon>
</customUI>

Notice that for the first time we have used the onLoad attribute of the customUI which will fire the callback VBA called loadRibbon. This will enable us to capture the ribbon object and change stuff later 'on the fly' by invalidating it.

Hopefully by now you are able to read and understand this basic format that makes the core of any customisation, remember, you must have an id for every control, within a collection, (remember the ribbon can only be singular, so doesn't need one), and this must be unique in your project and either is a custom (created by you) id, or a built-in (created by Microsoft) idMso. You will find this rule throughout your customisations, for example, above we use insertAfterMso meaning insert this tab after the built-in 'add-ins' one.

Adding A Group and Button

To the same code above, we can add our first custom group with a custom button. This button has it's own onAction callback to VBA to handle the code when the button is pressed. The code at this stage is as below:

<customUI onLoad="loadRibbon" xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon startFromScratch="false">
    <tabs>
      <tab id="myTab1" label="Nick's Tab" insertAfterMso="TabAddIns" visible="true" keytip="Z">
        <group id="Group1" label="Button" visible="true">
            <button id="Button1" imageMso="FileOpen" keytip="A" label="Button 1" 
                onAction="OpenFileButton" screentip="This is the screentip of button1" 
                    size="large" supertip="This is the supertip of button1"/>
         </group>
    </tab>
    </tabs>
  </ribbon>
</customUI>

 visible="true">
            <button id="Button1" imageMso="FileOpen" keytip="A" label="Button 1" 
                onAction="OpenFileButton" screentip="This is the screentip of button1" 
                    size="large" supertip="This is the supertip of button1"/>
         </group>
    </tab>
    </tabs>
  </ribbon>
</customUI>

The group has an id, is visible and has a label, which is the description that sits at the bottom on the group, as the below image of our 'growing tab shows. Hopefully you can also pick the other 'bits' from the code that created it, e.g. The tab label, where it's positioned, the size of the button, (large), that we have 'nicked' the imageMso for file>open and what a screentip and supertip look like.

 

Having a 'large' button exposes another feature of the ribbon and that is re-sizing. It will endeavour, as above to show it using it's full size, but if the overall size of the application window does not allow, it will first drop it's label and then shrink it's size down to small.

At this stage of course, loading the file will fire the onLoad callback and as there is no code it will return an error that the relevant code is not available. We'll take a look at the callback code once we have built our tab.

Buttons, Boxes and Separators.

One button in a group, as in the previous section is fine, but as soon as you add more buttons in a group, coupled with the 're-size' effect and we must use some 'hidden' controls, to stop the controls from flowing in undesirable directions. We use Boxes to achieve that, together with separators. Below is the code to add a new group ("Group2") and two 'sets' of three buttons, in vertical boxes with a separator dividing the two 'sets' of three. Whilst you cannot see the boxes, I have drawn them in the resulting tab image below the code, so you can visualise it in your own mind. You can of course see the 'vertical' separator. (It is only a vertical separator as it sits between two vertically aligned boxes, even in the code as you can see!).

<customUI onLoad="loadRibbon" xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon startFromScratch="false"> 
        <tabs> 
            <tab id="myTab1" label="Nick's Tab" insertAfterMso="TabAddIns" visible="true" keytip="Z">
                <group id="Group1" label="Button" visible="true"> 
                    <button id="Button1" imageMso="FileOpen" keytip="A" label="Button 1"
                        onAction="OpenFileButton" 
                        screentip="This is the screentip of button1" 
                        size="large" supertip="This is the supertip of button1"/> 
                </group>
    
                <group id="Group2" label="Boxes and Buttons">
                    <box id="box1" boxStyle="vertical"> 
                        <button id="button1B" label="Button 1" onAction="ButtonPressedMsgBox"/> 
                        <button id="button2B" label="Button 2" onAction="ButtonPressedMsgBox"/>
                        <button id="button3B" label="Button 3" onAction="ButtonPressedMsgBox"/>
                    </box> 
                    <separator id="separator1"/> 
                    <box id="box2" boxStyle="vertical">
                        <button id="buttonA" label="Button A" onAction="ButtonPressedMsgBox"/>
                        <button id="buttonB" label="Button B" onAction="ButtonPressedMsgBox"/> 
                        <button id="buttonC" label="Button C" onAction="ButtonPressedMsgBox"/>
                    </box> 
                    </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

If we now do the same with two sets of buttons in horizontal boxes, you will hopefully see the difference, both in the code and the ribbon image. Remember, horizontal buttons do not have a corresponding horizontal separator, separators do not have a style attribute...maybe v.Next!

<customUI onLoad="loadRibbon" xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon startFromScratch="false"> 
        <tabs> 
            <tab id="myTab1" label="Nick's Tab" insertAfterMso="TabAddIns" visible="true" keytip="Z">
                <group id="Group1" label="Button" visible="true"> 
                    <button id="Button1" imageMso="FileOpen" keytip="A" label="Button 1"
                        onAction="OpenFileButton" 
                        screentip="This is the screentip of button1" 
                        size="large" supertip="This is the supertip of button1"/> 
                </group>
    
                <group id="Group2" label="VBoxes and Buttons">
                    <box id="box1" boxStyle="vertical"> 
                        <button id="button1B" label="Button 1" onAction="ButtonPressedMsgBox"/> 
                        <button id="button2B" label="Button 2" onAction="ButtonPressedMsgBox"/>
                        <button id="button3B" label="Button 3" onAction="ButtonPressedMsgBox"/>
                    </box> 
                    <separator id="separator1"/> 
                    <box id="box2" boxStyle="vertical">
                        <button id="buttonA" label="Button A" onAction="ButtonPressedMsgBox"/>
                        <button id="buttonB" label="Button B" onAction="ButtonPressedMsgBox"/> 
                        <button id="buttonC" label="Button C" onAction="ButtonPressedMsgBox"/>
                    </box>
                    </group>

                <group id="Group3" label="HBoxes and Buttons">
                    <box id="box3" boxStyle="horizontal"> 
                        <button id="button1C" label="Button H1" imageMso="FileOpen" onAction="ButtonPressedMsgBox"/> 
                        <button id="button2C" label="Button H2" imageMso="FileOpen" onAction="ButtonPressedMsgBox"/>
                    </box>
                    <box id="box4" boxStyle="horizontal">
                        <button id="buttonHA" label="Button HA" onAction="ButtonPressedMsgBox"/>
                        <button id="buttonHB" label="Button HB" onAction="ButtonPressedMsgBox"/> 
                    </box>
                    <box id="box5" boxStyle="horizontal">
                        <button id="buttonHZ" label="Button HZ" onAction="ButtonPressedMsgBox"/>
                        <button id="buttonHX" label="Button HX" onAction="ButtonPressedMsgBox"/> 
                    </box>
                    </group>

            </tab>
        </tabs>
    </ribbon>
</customUI>

 

You may notice in the above code snippets that we are using the same callback on the button onAction attribute. This allows us to make a decision based on the button pressed, using the passed 'parent buttons' id, like so. For a button callback there are three captured properties, id, context and tag. Any or all of these can be used, stored in variables, on worksheets, in range names, etc.

Sub OpenFileButton(control As IRibbonControl)
    If control.ID = "button1" Then
        'do this
    Else
        'do this
    End If
End Sub

The Gallery and dialogLauncher Controls.

Lastly for this part, we will look at two new controls in this first iteration of the ribbon. The gallery, (this is like the styles dropdown image store) and the dialogLauncher, which is a container control, carrying a button, that looks like a little diagonal arrow in the bottom right hand corner of a group, allowing you to fire a dialog (built-in or custom) on it being pressed.

The Gallery

Currently you cannot customise built-in galleries, but you can build your own and load your image collateral through the Custom UI Editor. This part is simple, just open the editor, click on the image toolbar button, navigate to your image files and select OK. This will load them in the side of the UI editor so they an be selected and loaded into the file. A gallery is once again a container control, with 'items' below it that actually hold the images and fire callbacks, etc. It can also contain buttons, but these must appear below any items and they are the menu options that sit below the gallery items, like 'New Cell Style,,,' on the cell style gallery on the home tab.

The added code is below, with the image of the resulting controls below that:

<customUI onLoad="loadRibbon" xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon startFromScratch="false"> 
        <tabs> 
            <tab id="myTab1" label="Nick's Tab" insertAfterMso="TabAddIns" visible="true" keytip="Z">
                <group id="Group1" label="Button" visible="true"> 
                    <button id="Button1" imageMso="FileOpen" keytip="A" label="Button 1"
                        onAction="OpenFileButton" 
                        screentip="This is the screentip of button1" 
                        size="large" supertip="This is the supertip of button1"/> 
                </group>
    
                <group id="Group2" label="VBoxes and Buttons">
                    <box id="box1" boxStyle="vertical"> 
                        <button id="button1B" label="Button 1" onAction="ButtonPressedMsgBox"/> 
                        <button id="button2B" label="Button 2" onAction="ButtonPressedMsgBox"/>
                        <button id="button3B" label="Button 3" onAction="ButtonPressedMsgBox"/>
                    </box> 
                    <separator id="separator1"/> 
                    <box id="box2" boxStyle="vertical">
                        <button id="buttonA" label="Button A" onAction="ButtonPressedMsgBox"/>
                        <button id="buttonB" label="Button B" onAction="ButtonPressedMsgBox"/> 
                        <button id="buttonC" label="Button C" onAction="ButtonPressedMsgBox"/>
                    </box>
                    </group>

                <group id="Group3" label="HBoxes and Buttons">
                    <box id="box3" boxStyle="horizontal"> 
                        <button id="button1C" label="Button H1" imageMso="FileOpen" onAction="ButtonPressedMsgBox"/> 
                        <button id="button2C" label="Button H2" imageMso="FileOpen" onAction="ButtonPressedMsgBox"/>
                    </box>
                    <box id="box4" boxStyle="horizontal">
                        <button id="buttonHA" label="Button HA" onAction="ButtonPressedMsgBox"/>
                        <button id="buttonHB" label="Button HB" onAction="ButtonPressedMsgBox"/> 
                    </box>
                    <box id="box5" boxStyle="horizontal">
                        <button id="buttonHZ" label="Button HZ" onAction="ButtonPressedMsgBox"/>
                        <button id="buttonHX" label="Button HX" onAction="ButtonPressedMsgBox"/> 
                    </box>
                    </group>

                    <group id="Group4" label="Gallery">
                    <gallery id="Gallery1" label="Pictures" imageMso="HeaderFooterPictureInsert"
                        screentip="What have we here?" onAction="ClickImage" columns="2" rows="3" 
                        itemWidth="150" showItemLabel="false" itemHeight="150" size="large">
                        <item id="pic1" image="pic1" label="Picture 1" screentip="This is picture1" />
                        <item id="pic2" image="pic2" label="Picture 2" screentip="This is picture2" />
                        <item id="pic3" image="pic3" label="Picture 3" screentip="This is picture3" />
                        <item id="pic4" image="pic4" label="Picture 4" screentip="This is picture4" />
                        <item id="pic5" image="pic5" label="Picture 5" screentip="This is picture5" />
                        <item id="pic6" image="pic6" label="Picture 6" screentip="This is picture6" />
                        <!--Buttons at bittom of gallery menu-->
                        <button id="galleryMenuButton1" label="Fetch pictures..." imageMso="PictureInsertFromFile" 
                            onAction="FetchPictures"/>
                        <button id="galleryMenuButton2" label="Become an MVP..." image="mvp" 
                            onAction="BecomeMVP"/>
                    </gallery>
                    </group>

            </tab>
        </tabs>
    </ribbon>
</customUI>

We can add a dialogLauncher to the group that contains this gallery control now. IT MUST BE THE LAST CONTROL IN THE GROUP! It must also of course have a button to create the action required. The additional code is below (just the gallery control part with it added) and the resultant control in the corner of the group (with the cursor next to it).

<group id="Group3" label="Gallery">
<gallery id="Gallery1" label="Pictures" imageMso="HeaderFooterPictureInsert" 
    screentip="What have we here?" onAction="ClickImage" columns="2" rows="3" 
    itemWidth="150" showItemLabel="false" itemHeight="150" size="large">
    <item id="pic1" image="pic1" label="Picture 1" screentip="This is picture1" />
    <item id="pic2" image="pic2" label="Picture 2" screentip="This is picture2" />
    <item id="pic3" image="pic3" label="Picture 3" screentip="This is picture3" />
    <item id="pic4" image="pic4" label="Picture 4" screentip="This is picture4" />
    <item id="pic5" image="pic5" label="Picture 5" screentip="This is picture5" />
    <item id="pic6" image="pic6" label="Picture 6" screentip="This is picture6" />
    <!--Buttons at bittom of gallery menu-->
    <button id="galleryMenuButton1" label="Fetch pictures..." imageMso="PictureInsertFromFile" onAction="FetchPictures"/>
    <button id="galleryMenuButton2" label="Become an MVP..." image="mvp" onAction="BecomeMVP"/>
</gallery>
  <!--This group has a dialogBoxLauncher and button control which adds-->
  <!--the little arrow on bottom right of group Make it last control element in group-->
  <dialogBoxLauncher>
    <button id="myLauncher" label="Dialog Launcher" screentip="This is a screentip" supertip="This is a supertip"
    onAction="LaunchDialog" />
    <!--The dialogBoxLauncher is a container control-->
    <!--It needs a button control to add an onAction attribute-->
  </dialogBoxLauncher>
</group>

That's going to be all for this part, in the final part we will explore the remaining popular controls, take a little more look at the callback signatures and attempt to invalidate the ribbon and change controls on the fly. Hopefully you're enjoying it...stay tuned!

Posted Feb 14 2008, 11:56 PM by Nick Hodge
Filed under: , ,

Comments

sam wrote re: Ribbon, Step-by-Step. Part 4 (Exploring Other Controls)
on Sat, Feb 16 2008 9:55 AM

Nick,

I want to add a simple button like the copy button.

When I point to it I want a tooltip to appear.

Is this possible in 2007

Sam

Nick Hodge wrote re: Ribbon, Step-by-Step. Part 4 (Exploring Other Controls)
on Sat, Feb 16 2008 1:30 PM

Sam

Can you expand on what you are trying to do? Is it addirional custom functionality, (macro) or the existing copy functionality?

If it is just the standard functionality, you are better to add that to the QAT, if it is custom functionality, then you will either need to add a custom group, or re-assign an existing button.

Let me know a bit more detail.

sam wrote re: Ribbon, Step-by-Step. Part 4 (Exploring Other Controls)
on Sun, Feb 17 2008 9:16 AM

Hi Nick,

Just discoverd it ...its called Supertip ....

Thanks

Nick Hodge wrote re: Ribbon, Step-by-Step. Part 4 (Exploring Other Controls)
on Sun, Feb 17 2008 11:03 AM

Sam

Right, check out screentip too. It's shown which is which in an image near the top of this article. If you download the ribbon pack from the file download area, it also gives the popular attributes next to all the controls.

Mike McGavin wrote re: Ribbon, Step-by-Step. Part 4 (Exploring Other Controls)
on Tue, Apr 15 2008 6:13 PM

Hi Nick.

Firstly thanks for the comprehensive guide to ribbon manipulation that you've put it together.  I'm not exclusively working with the ribbon in Excel, and I've found it to be very useful for understanding the ribbon in all Office apps..

This might turn out to have a very obvious answer but I've been struggling with it for a week now, and it seems to fit with the topic of this post where you've covered groups and wrapping:

 Have you come across any tips about how the ribbon's groups collapse into galleries?

I've noticed that if I make the Excel window smaller to reduce available ribbon space, built-in groups (such as the Alignment, Number, Styles and Cells groups on the Home tab to name a few) all collapse into gallery-like controls, and effectively become drop-down menus.

This is an effect that I just haven't been able to mimic with my own custom groups, despite having looked through all the attributes and settings I've been able to find for the group element, and hunting through a variety of forums looking for people discussing it.

Any group that I create on the ribbon will keep its original static size and layout, even though I don't think the controls I'm placing on it are likely to be as useful as the built-in controls that are collapsing to keep the space available for it when the window size is limited.

When there's simply no space left, my group remains fully laid-out, and I get the slider arrows on both ends of the ribbon to scroll it from left to right. I'd much rather it just collapsed into a gallery, preferably before all of the built-in groups did so.

I'm hoping that I've just missed something really obvious, although I *have* noticed that the integration controls from at least one major commercial addin (specifically Interwoven's Worksite) don't seem to collapse properly into galleries as the built-in ones do, either.  This makes me curious if it's even possible for third party extensions, since I'd have thought that a company like Interwoven would have the resources to research this kind of thing comprehensively.

That's my current problem in any case.  If you have any ideas I'd really like to hear them.

Thanks.

Mike.

Nick Hodge wrote re: Ribbon, Step-by-Step. Part 4 (Exploring Other Controls)
on Wed, Apr 16 2008 12:37 AM

Mike

take comfort in the fact that auto resize is not current functionality on custom groups/controls. Maybe v.next?

It is definately a known problem. The built in controls drop from large to small to fly-outs and then eventually disappear if you make the window small enough, just not with custom ones.

At least you know not to keep searching ;-)

Mike McGavin wrote re: Ribbon, Step-by-Step. Part 4 (Exploring Other Controls)
on Wed, Apr 16 2008 5:04 PM

Yep, that's true.  Thanks for helping to clarify it for me.  I guess we might need to re-think whereabouts we want to place these controls.

Nick Hodge wrote re: Ribbon, Step-by-Step. Part 4 (Exploring Other Controls)
on Wed, Apr 16 2008 7:15 PM

Mike

I would say so, but I think these things will be more flexible next time.

Nick Hodge wrote re: Ribbon, Step-by-Step. Part 4 (Exploring Other Controls)
on Wed, Apr 16 2008 7:15 PM

Mike

I would say so, but I think these things will be more flexible next time.

Hognar wrote re: Ribbon, Step-by-Step. Part 4 (Exploring Other Controls)
on Fri, Apr 18 2008 4:23 PM

Hi,

How can I handle le Key F1 visible when I mouse over the button. Which attribute can I write within my XML ? onKeyPress ?

Thanks a lot

Hognar

Nick Hodge wrote re: Ribbon, Step-by-Step. Part 4 (Exploring Other Controls)
on Sun, Apr 20 2008 7:15 AM

Hognar

Not quite with you is it the F1 help from a control you want to get working.

Do you want to show your help or just trap the event of F1 when it's pressed in that context?

Agus Surachman wrote re: Ribbon, Step-by-Step. Part 4 (Exploring Other Controls)
on Sun, Apr 20 2008 8:43 PM

Nick.

I'm wondering how to create button which has stype like Macro button or Paste button?

It appears as one button but when we hover it, it apprears two seperate button.

Please your help.

Nick Hodge wrote re: Ribbon, Step-by-Step. Part 4 (Exploring Other Controls)
on Mon, Apr 21 2008 8:44 AM

Agus

This would be a splitButton and addressed like so

<splitButton id="split1" size="large">

<button id="buttonSplit" imageMso="PropertySheet" label="Click Here"/>

<menu id="splitMenu" itemSize="large">

<button id="splitbutt1" imageMso="AnimationAudio" label="Turn up the volume" onAction="TurnUpVol"/>

<button id="splitbutt2" imageMso="AutoDial" label="Make a call" onAction="MakeACall"/>

<button id="splitbutt3" imageMso="MarkAsUnread" label="Put it in the post" onAction="PutInPost"/>

<!--This is a sub-menu-->

<menu id="submenu1" label="Extra Items" imageMso="TentativeAcceptInvitation">

<button idMso="FileOpen"/>

<button idMso="FilePrintPreview"/>

<button idMso="FilePrintQuick"/>

</menu>

</menu>

</splitButton>

Agus Surachman wrote re: Ribbon, Step-by-Step. Part 4 (Exploring Other Controls)
on Mon, Apr 21 2008 8:40 PM

Thank you Nick.

Fantastic

Agus Surachman wrote re: Ribbon, Step-by-Step. Part 4 (Exploring Other Controls)
on Tue, Apr 22 2008 6:57 AM

Nick.

Is that possible to change the content of Ribbon on-the-fly (dynamically)?

For example the menu button can be added or deleted conditionally, and also other properties such as the picture.

Nick Hodge wrote re: Ribbon, Step-by-Step. Part 4 (Exploring Other Controls)
on Tue, Apr 22 2008 8:46 AM

Agus

It is sort of possible to do stuff with ribbon controls on the fly. I will need to get around to writing the last bit.

The theory is this:

You use an onLoad attribute in you customUI declaration like so

<customUI xmlns="schemas.microsoft.com/.../customui"

onLoad="myRibbon_onLoad">

You will notice when generating the callback for this it will pass you a ribbon object. In this callback, in a standard VBA module, capture this ribbon object and assign it to a global variable (outside and above any procedures in the module). e.g

Public myRibbon As IRibbonUI

Sub myRibbon_onLoad(ribbon As IRibbonUI)

   Set myRibbon = ribbon

End Sub

Now you have the ribbon captured and can use it's Invalidate property to force a rebuild (You can rebuild an inividual control using it's InvalidateControl property also and pass in a controlID)

So, for example, you may trigger the Invalidate from a button_click() event on a ribbon control and then use any of the 'get' attributes (getImage, getLabel, etc) to rebuild other controls.

That is probably as clear as mud, so I will try and get around to the last part this weekend. I may even put some videos together :-)

Agus Surachman wrote re: Ribbon, Step-by-Step. Part 4 (Exploring Other Controls)
on Tue, Apr 22 2008 8:06 PM

Nice.

I'm waiting for the last part eagerly. :)

vba insert rows columns wrote vba insert rows columns