Hi Mohan,
Sorry about not realizing you are working on Office 2007 and using Ribbon.xml to customize your Office UI. The thread I posted above is target at the scenario that we create the button using CommandBar and CommandBarControl. So we can create a VBA Module get the CommandControl’s handle and call it Execute() method. And we can assign a shortcut key for the VBA Module. And then save the module as a Excel Add In.
If we create the button via customize ribbon.xml, we cannot get the button’s handle in VBA. So the above way is stumped this time!
My thought is that, we should still use VBA to assign the shortcut key, but alternatively we should expose an ExcelAddin Automation Service to let VBA call our function. I will show the process step by step (I am writing C# example. I think you can convert it to VB version without much effort. And if you have any difficulty about that, please feel free to let me know). And if you want to understand this post better, you can refer an article on Andrew Whitechapel’s blog first, here: http://blogs.msdn.com/andreww/archive/2007/01/15/vsto-add-ins-comaddins-and-requestcomaddinautomationservice.aspx
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. Add the same Ribbon description to the ribbon.xml file as yours
3. Using the following call back: (We will implement addinUtilities.CallBlueUl later)
Code Block
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 like:
Code Block
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, and make it implement the interface IAddinUtilities, codes like:
Code Block
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.
Code Block
public partial class ThisAddIn
{
public AddinUtilities addinUtilities;
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:
Code Block
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
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. The bottom of the range will be changed to Color pink. (It seems the color index 7 is pink, however, that does not related and be the matter)
That’s all! If you have any doubt about the process, please feel free to let me know, I will try my best to make clarification.
Thanks
Ji