How to Assign Shortcut Key to Buttons created via VSTO Excel 2007 Add In

原创 2007年10月09日 22:30:00

How to Assign Shortcut Key to Buttons created via VSTO Excel Add In

            Today, I was asked how to assign a shortcut key to buttons created in VSTO Excel 2007 Add In, like “Ctrl+Shift+B”. Unfortunately, in VSTO, it is not supported to assign a shortcut key to buttons directly. We can only do it in VBA module.

                As I described in another article in my blog, we have two ways to create buttons in VSTO SE project. One way is adding a CommandBar and CommandBarControl. Excel will put the button in, by default, Add-ins Tab->Custom Toolbars Group. Another way is using Ribbon support item provided by VSTO SE. We will customize our Ribbon button in Ribbon.xml file, and implement their call back functions in Ribbon.cs.

                As to the first way, it is easy for us to make a VBA module call a button’s corresponding method. We should get the button’s handle and invoke its Execute() method. With above done, we can assign a shortcut key to the VBA module. Every time we press the our defined shortcut key, Excel will call the VBA module, and the VBA module will perform the button’s Execute() method.

                But as to the second way, life becomes dark because, we cannot get the button’s handle any longer. After a lot of thinking, the ComAddInAutomationService flashes into my brain. Yeah, that is the trick! I will post the specific steps about how to achieve that as following. But if you want have a better understanding about the reason why and the way how it works, I recommend you to read an article written by Andrew Whitechapel(The senior program manager in Microsoft VSTO team). Here is the link: http://blogs.msdn.com/andreww/archive/2007/01/15/vsto-add-ins-comaddins-and-requestcomaddinautomationservice.aspx

                OK. The following is my solution step by step:

1.       I create an Excel Add In named ExcelAddIn using VSTO SE and Excel 2007

2.       I add a Ribbon support Item to my project. Modify the ribbon.xml like the following:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">

<ribbon>

<tabs>

<tab id="CustomTab" label="My Menu" visible ="true">

<group id="MyGroup" label="My Group">

<button id="BlueUL" label="Blue UL" size="normal" onAction="MyModule" />

</group>

</tab>

</tabs>

</ribbon>

</customUI>

 

3.       Using the following call back to react the button’s onAction event: (We will implement addinUtilities.CallBlueUl later)

        public void MyModule(Office.IRibbonControl control)

        {

            switch (control.Id)

            {

                case "BlueUL":

                    Globals.ThisAddIn.addinUtilities.CallBlueUl();

                    break;

 

                default:

                    break;

            }           

        }

4.       Add an interface named IAddinUtilities in the project. Make it look like:

using System;

using System.Collections.Generic;

using System.Text;

using System.Runtime.InteropServices;

using Office = Microsoft.Office.Core;

using Excel = Microsoft.Office.Interop.Excel;

 

namespace ExcelAddIn

{

    [ComVisible(true)]

    [Guid("B523844E-1A41-4118-A0F0-FDFA7BCD77C9")]

    [InterfaceType(ComInterfaceType.InterfaceIsDual)]

 

    public interface IAddinUtilities

    {

        void CallBlueUl();

}

}

5.       Add a Class named AddinUtilities to our project, and make it implement the interface IAddinUtilities, codes like:

using System;

using System.Collections.Generic;

using System.Text;

using System.Runtime.InteropServices;

using Excel = Microsoft.Office.Interop.Excel;

using Office = Microsoft.Office.Core;

 

namespace ExcelAddIn

{

    [ComVisible(true)]

    [ClassInterface(ClassInterfaceType.None)]

 

    public class AddinUtilities : IAddinUtilities

    {

        public void CallBlueUl()

        {

            Excel.Range range = Globals.ThisAddIn.Application.Selection as Excel.Range;

 

            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;

            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;

            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].ColorIndex = 7;

        }

     }

                  }

6.       In ExcelAddIn Class, we override the RequestComAddInAutomationService() to return a new instance of the Class we created above. And make the field as public, which makes the call in step 3 can be compiled.

    public partial class ThisAddIn

    {

        public AddinUtilities addinUtilities;  //note the public

 

        protected override object RequestComAddInAutomationService()

        {

            if (addinUtilities == null)

            {

                addinUtilities = new AddinUtilities();

            }

            return addinUtilities;

        }

     }

7.       Compile and run the project. The Excel application shows up. Click Developer Tab->Code Group->Macros Button. In pop up window, you type the Macro Name you want. Typically, we use CallBlueUl here. And then click Button Create.

8.       The Microsoft Visual Basic IDE pops up, and copy the following codes into the module, and make it look like:

Sub CallBuleUl()

    Dim addin As Office.COMAddIn

    Dim automationObject As Object

    Set addin = Application.COMAddIns("ExcelAddin")

    Set automationObject = addin.Object

    automationObject.CallBlueUl

End Sub

9.       Close the Microsoft Visual Basic IDE. Again, we click Developer Tab->Code Group->Macros Button. In the pop up window, we click Button Options….Press B with the hold of Shirt. You will see the Shortcut key is assigned as Ctrl+Shift+B.(We can only assign Ctrl+Shift shortcut key, but not Ctrl+Alt). Click OK.

10.   Save the workbook as an Excel Add-In. To do this, you can choose the file type to be Excel Add-In(*.xlam) in Save As dialog. Typically, the name is Book1.xlam. Do not change the default location of the Book1.xlam

11.   Click Office Button->Excel Options->Add-Ins Tab, choose Excel Add-ins, click Go. In the pop up Add-Ins Window, check the box Book1, and then click OK.

12.   Select a few cells and press Ctrl+Shift+B. Color of the bottom of the range will be changed to pink. (It seems the color index 7 is pink, however, that it not related to topic here and does not matter)

That’s all! Oh, it is 10:30 already. Time to go home! A nice day!

 

How To Create Buttons in Cocos2D: Simple, Radio, and Toggle

How To Create Buttons in Cocos2D: Simple, Radio, and ToggleLike this post? Follow me on Twitter!Butt...

[Headset] How to add a headset music key

[Headset] How to add a headset music key [Description] How to add aHeadset Musickey on mt6577/...

在cadence中添加pspice(How to add p-spice lib in cadence)

How to add p-spice lib in cadence. 1.      First, you need to download aspice lib online and name it...
  • hjl240
  • hjl240
  • 2016年03月11日 10:38
  • 1639

How to add the patch in apache community?

1、开发环境准备: 要对apache hadoop 源码进行开发,首先需要搭建本地hadoop源码开发环境。本地Linux系统需要安装JDK1.8, maven,ProtocolBuffer2....

How to add SOA Server (any FMW) Target in EMGC 11g

How to add SOA Server (any FMW) Target in EMGC 11g? Steps for discovering SOA (any FMW) target: ...

How to Add/Drop/Resize Redo Log with Physical Standby in place. [ID 473442.1]

How to Add/Drop/Resize Redo Log with Physical Standby in place. [ID 473442.1] ...

How to add 3G modem in Sabresx board

How to add 3G modem in Sabresx board 由 waterzhou 在 2014-6-30 上午2:05 上创建,最后由 waterzhou 在 2014-...
  • jack_a8
  • jack_a8
  • 2015年01月25日 18:25
  • 1312

【iMX6QD】 How to Add 24-bit LVDS Support in Android

目录(?)[+] Introduction   LVDS display panel driving data flow: Display quality: To get...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:How to Assign Shortcut Key to Buttons created via VSTO Excel 2007 Add In
举报原因:
原因补充:

(最多只允许输入30个字)