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

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
on Mon, Jun 23 2008 9:29 AM

Pingback from  vba insert rows columns

zhuangjun wrote re: Ribbon, Step-by-Step. Part 4 (Exploring Other Controls)
on Wed, Sep 3 2008 11:15 PM

i come from china and very like your blog,i got more help from your blog,now i have a question ,

in these code

<item id="pic1" image="pic1" label="Picture 1" screentip="This is picture1" />

i want to know where is pic1's place?

create a folder in book1.xlsx.zip??

Nick Hodge wrote re: Ribbon, Step-by-Step. Part 4 (Exploring Other Controls)
on Thu, Sep 4 2008 1:46 AM

zhuangjun

If you use the CustomUI editor (See Part 1 of the Ribbon series), as I have done then you just simply upload them to that.

If you are doing in by hand, (but I wouldn't advise this), then yes, it simply sits in a folder in the zip archive and needs referencing in the relevant _rels file.

Honestly, use the CustomUI editor...no need to 're-invent the wheel'

zhuangjun wrote re: Ribbon, Step-by-Step. Part 4 (Exploring Other Controls)
on Thu, Sep 4 2008 3:28 AM

NIck

thanks a lot,i do it and get a good result.

Antonio wrote re: Ribbon, Step-by-Step. Part 4 (Exploring Other Controls)
on Wed, Sep 10 2008 6:47 AM

Dear sir

I want to formule a question

I am using the ribbon in my application, when the ribbon loads I asign the ribbon to a Public variable

But my proble is when the progrmas give an error or when I stop the code , I lose the variables an the public variable  ribbon set to nohing

My question is this:

Is it possible to assign again the public variable to a ribbon whitout exit of application and enter again

The unic way to assign this variable is in the event onload of the ribbon

Thanks in advance

Nick Hodge wrote re: Ribbon, Step-by-Step. Part 4 (Exploring Other Controls)
on Wed, Sep 10 2008 3:50 PM

You will only be able to invalidate and therefore re-load the ribbon if you have the ribbon object, which you don't, so in your case no, although you should of course have error handling code to overcome this

gabethegrape wrote re: Ribbon, Step-by-Step. Part 4 (Exploring Other Controls)
on Fri, May 8 2009 12:48 AM

Nick,

Is it possible to have one add-in create the custom ribbon item and a second add-in to appear as an item in that ribbon?

Cheers,

Gabe

Nick Hodge wrote re: Ribbon, Step-by-Step. Part 4 (Exploring Other Controls)
on Fri, May 8 2009 3:28 AM

Gabe

No that is not currently possible as i understand it, the first add-in loaded loads all the ribbon customisations

barkey147 wrote re: Ribbon, Step-by-Step. Part 4 (Exploring Other Controls)
on Mon, Jun 15 2009 2:30 PM

Nick,

Thanks for the great tutorial!  I'm not an expert by any means but this was very easy to understand.  I was able to use these guides to add a button to the "Home" tab for simply inserting an arrow (ShapeStraightConnectorArrow).  The button appears fine in the Ribbon, but i don't know how to assign it the correct excel function of inserting the ShapeStraightConnectorArrow.  What is the coding for the module to make the button function like that?Or is it simply possible to assign onAction="ShapeStraightConnectorArrow"

Here's my code for the ribbon button.

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

<ribbon>

<tabs>

<tab idMso="TabHome">

<group id="Group1" insertAfterMso="GroupEditingExcel" label="My Group" visible="true">

<button id="Button1" imageMso="ShapeStraightConnectorArrow" keytip="A" label="Insert Arrow" onAction="Arrow"/>

</group>

</tab>

</tabs>

</ribbon>

</customUI>

Do i need the module?  how would it work?

Sub Arrow(control As IRibbonControl)

If control.ID = "Button1" Then

'What goes here?????'

Else 'Do i need this????'

End If

End Sub

Thanks,

Travis

Nick Hodge wrote re: Ribbon, Step-by-Step. Part 4 (Exploring Other Controls)
on Mon, Jun 15 2009 5:58 PM

Travis

You RibbonX needs a little correction and the code below that will draw and arrow similar to the default one on the active worksheet. Place the VBA code in a standard module in a macro-enabled file (xlsm)

RibbonX code

<customUI xmlns="schemas.microsoft.com/.../customui"> <ribbon startFromScratch="false">  

<tabs>

<tab idMso="TabHome">

<group id="Group1" insertAfterMso="GroupEditingExcel" label="My Group" visible="true">

<button id="Button1" imageMso="ShapeStraightConnectorArrow" keytip="A" label="Insert Arrow" onAction="drawArrow"/>

</group>

</tab>

</tabs>

</ribbon>

</customUI>

VBA Code

Sub drawArrow(control As IRibbonControl)

Dim wks As Worksheet

Set wks = ActiveSheet

With wks.Shapes.AddLine(100, 100, 150, 200).Line

   .EndArrowheadStyle = msoArrowheadOpen

End With

End Sub

barkey147 wrote re: Ribbon, Step-by-Step. Part 4 (Exploring Other Controls)
on Tue, Jun 16 2009 9:59 AM

Thanks Nick!  

I also figured out i could simplify it like this

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

<ribbon>

<tabs>

<tab idMso="TabHome">

<group id="Group1" insertAfterMso="GroupEditingExcel" label="Shortcuts" visible="true">

<toggleButton idMso = "ShapeStraightConnectorArrow"/>

</group>

</tab>

</tabs>

</ribbon>

</customUI>

Now is there a way that i can have this "shortcut" button show up automatically when i open a new blank document?  In other words Is there a way to modify the default blank template?

barkey147 wrote re: Ribbon, Step-by-Step. Part 4 (Exploring Other Controls)
on Tue, Jun 16 2009 10:49 AM

and when i open exsisting excel files, i have no Custom UI.  how can i get the cutsom UI buttons just to always be there no matter what? Is that possible?

thanks for the help

Travis

Nick Hodge wrote re: Ribbon, Step-by-Step. Part 4 (Exploring Other Controls)
on Wed, Jun 17 2009 1:47 AM

Sure

As explained using a controls idMso brings it in with it's built-in functionality.

If you want this available everytime you open Excel, put the RibbonX in your personal.xlsm file or in a workbook in your xlStart folder

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.