Programmatically add buttons to quick access toolbar

Article
2011-08-22

Question

_{Monday, August 22, 2011 9:22 PM}

VS2010 Pro, Office 2007

I have googled and searched MSDN for what should be a simple solution to my question.

How do I leverage VSTO to add some buttons to the QAT for specific Workbooks? Right now, I am using an Workbook specific Ribbon to add these button, but that does not provide the user experience that I want. The QAT is ideal for what I want.

In the Ribbon implementation, a button click will fire off a small VB.Net procedure (in the Ribbon Class instance) which, it turn, displays a Windows.Forms Form. All is good except that it takes an extra click on the menu line to get my Ribbon displayed.

I have considered adding a VBA module to contain procedures that call back to .Net to accomplish the same thing, but I am trying to do this without any VBA.

All replies (9)

_{Tuesday, August 23, 2011 4:01 PM ✅Answered | 1 vote}

Hi Jim

<<To clarify: I am working in VSTO and the Ribbon Designer.>>

Thank you :-)

You will need to recreate the Group, but should be able to re-use the code.

Right-click next to any TAB in the Ribbon Designer. Select the command Add Ribbon Tab.

In the Properties window, display the hidden members of the ControlID property. From the list "ControlIdType" select "Office". Assign the following to the OfficeID property, just below: TabHome.

Right-click your custom group and choose Copy. Move to the TabHome (Built-in) tab, right-click and choose Paste.

By default, the group will appear at the end of the Home Tab.

Your other requirement, that it only displays for certain workbooks, means you'll need to monitor the appropriate events in your add-in, such as WorkbookOpen and WorkbookClose. In those events you can control the visibility of your Tab using code something like this:

Dim r As Ribbon1 = Globals.Ribbons(1) 'Assumes the class name is Ribbon1
r.Tab1.Visible = TrueCindy Meister, VSTO/Word MVP

_{Tuesday, August 23, 2011 1:31 AM}

AFAIK that is not possible or maybe I should say, I haven't heard of a way to do it.Kind Regards, Rich ... http://greatcirclelearning.com

_{Tuesday, August 23, 2011 3:16 AM}

OK, I was afraid of that. Since that seems to be the case, How about any one of three alternate approaches?

Remember, my only real desire here is to get my buttons visible (and usable) in the specific Workbook when that Workbook opens.

1. How would I simply attach my Group to the Home Tab, say immediately following the Editing group? (preferred solution).

2. How do I reference a proceedure located in -- for example -- the ThisWorkBook Class from VBA?

3. How to get my Tab to display at Workbook open time - just like it does now when I Click it in the Tab Select bar?

_{Tuesday, August 23, 2011 8:57 AM}

Hi Hardway

1. QAT: You can only affect the content of the QAT by using RibbonXML and setting startFromScratch="true", which means you'd need to effectively re-write the entire Ribbon

2. The QAT is meant for user customizations, which is why it's not "exposed" directly to the developer. There is a QAT file that holds the customizations, but this cannot be edited while the Office application is opened.

3. adding a group to the Home Tab: that's absolutely no problem, but if you want instructions you have to specifiy whether you're working with Ribbon XML or the VSTO Ribbon Designer.

4. Are you asking about calling back to VSTO code from VBA? Just double-checking... Have you consulted this page in the VSTO documentation on MSDN: http://msdn.microsoft.com/en-us/library/3hekt07s.aspx

5. I don't understand your last question. What's the "Tab Select" bar? Are you asking how to position your tab in front of the Home tab so that it's the default active tab?

Cindy Meister, VSTO/Word MVP

_{Tuesday, August 23, 2011 9:24 AM}

Hi HardWay,

If I follow your intention correctly, here are some suggestion for you:

1. For <<get my buttons visible in specific workbook>>

You can use create document-level VSTO solution based on the specific workbook, and do some ribbon customization to reach your target.

2. For <<immediately following the Editing group>>

In ribbon XML file, you can use inserAfterMso attribute to locate the custom group in the position as you like.

For these two requirements, I have written a sample for your reference:

<?xml version="1.0" encoding="UTF-8"?>
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="Ribbon_Load">
 <ribbon startFromScratch ="true">
 <qat>
  <sharedControls >
  <button idMso ="Copy"/>
  </sharedControls>
  <documentControls >
  <button idMso="Cut"/>
  </documentControls>
 </qat>
 <tabs>
  <tab idMso="TabHome" visible ="true">
  <group id="MyGroup" label="My Group" insertAfterMso ="GroupEditingExcel">
   <button id ="button2"
     label ="MyButton2"
     onAction ="button2_click"/>
  </group>
  </tab>
  <tab idMso ="TabInsert" visible ="true"/>
  <tab idMso ="TabPageLayoutExcel" visible ="true"/>
  <tab idMso ="TabFormulas" visible ="true"/>
  <tab idMso ="TabData" visible ="true"/>
  <tab idMso ="TabReview" visible ="true"/>
  <tab idMso ="TabView" visible ="true"/>
  <tab idMso ="TabDeveloper" visible ="true"/> 
  <tab idMso ="TabPrintPreview" visible ="true"/>
 </tabs>
 </ribbon>
</customUI>

You can learn more about this in the following links:

http://msdn.microsoft.com/en-us/library/aa338202(v=office.12).aspx

http://msdn.microsoft.com/en-us/library/aa338199(v=office.12).aspx

http://msdn.microsoft.com/en-us/library/aa722523(v=office.12).aspx

3. For <<reference a proceedure located in the ThisWorkbook Class from VBA>>

So you want to call .net code in VBA?

You can check this KB article:

http://support.microsoft.com/kb/317535

Generally, we create a .net class library and register it for COM Interop so that VBA code in Office program can add reference to this dll file. But I'm not sure if the workthrough works for VSTO class library...

In addition, if you want to call VBA code from .net, it will be very simple:

http://support.microsoft.com/kb/306683

If you have any further concern about these, please feel free to let me know.

Best Regards, Calvin Gao [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

_{Tuesday, August 23, 2011 2:53 PM}

Cindy & Calvin,

Thanks for the replies.

To clarify: I am working in VSTO and the Ribbon Designer. I have created a Custom Group with various buttons and backing code to do what I want to do. All works as intended.

My only question is how to get those buttons displayed when that one Workbook is loaded rather than having to click on the Custom Group name on the Tab Select bar of Excel.

I do not care which technique I use to do this. I asked 3 questions, any one of which might get me to this desired end. My preferred method would have the Custom Group displayed as "part of" the Home Tab where "part of" could be either:

Temporarily adding to the Home Tab - for this Workbook only.
Displayed, at Workbook open, in a position following the last standard group in the Home Tab.

Ribbon XML seems to be a bit of overkill for what should be a simple problem. I suspect that there is a Property that I can set in VS 2010 (VSTO) that will do this for me. I just don't know what it is or how to set it. Position and PositionType seem promising, but the details are not well documented (or at least I can't find & comprehend it).

I would prefer to not use VBA, but, if I have to, then my concern is the same as Calvin implied "But I'm not sure if the workthrough works for VSTO class library...". The devil is in the details here. I have not spent much (any?) time researching this question since the VBA solution is my last choice.

I will follow up on your references later today, but I suspect that I have already looked through most of them and not had a Eureka moment.

TIA,

Jim Parsells

_{Wednesday, August 24, 2011 12:24 AM}

Thanks Cindy.

Your most recent suggestion does exactly what I wanted to do. I had all the pieces, but did not know how to put them together. It would have taken me a long time to twig on to Adding the TabHome Tab to my Ribbon!

A couple of tweaks to your answer:

Cutting rather than Copying my custom group before Pasting onto TabHome saves a bit of fix up. After all, I do not need the extra Tab to show the same buttons twice. Once added to TabHome, I have no need for the original Custom Tab.
With somewhat limited testing and a bit of thought about how all this works has shown (so far) that no additional Event Handlers are required to limit this addition to TabHome to a single Workbook. At least in Debug mode, other Workbooks opened with the original still active do not (correctlly) show the addition. It only shows on the Workbook that the app is built around.

Thanks for the help,

Jim Parsells

_{Wednesday, August 24, 2011 3:35 AM}

Hi Jim.

Glad to hear you to get it work.

Just a kind remind, Ribbon XML is not such complex to understand and code. Actually, you can even convert Ribbon designer to XML code in designer editor by right clicking in anywhere within the ribbon and selecting "Export Ribbon to XML"

In my option, Ribbon XML is much easy to deliver what we want to do in forum communication, which is the reason I prefer to use it :-)

If you have any further question about VSTO, welcome to post here.

Have a nice day.

_{Wednesday, August 24, 2011 6:37 AM}

Hi Jim

<<At least in Debug mode, other Workbooks opened with the original still active do not (correctlly) show the addition. It only shows on the Workbook that the app is built around. >>

Ah, yes, of course. I'd forgotten that this is a workbook-level customization, not an add-in :-)

Glad it's working for you!

Cindy Meister, VSTO/Word MVP

Share via

Programmatically add buttons to quick access toolbar

Question

All replies (9)

Additional resources