Select VSTO Custom Ribbon in Excel

When VSTO code creates a list object and applies a table style in Excel 2007, the Table Tools/Design tab is activated automatically. When the user then selects a non-table cell, the Home tab is activated by default. Is there a way to either suppress this behavior or reset the active tab to the custom tab that started the action? What I would like is for the custom rib开发者_开发百科bon to be visible after the action that creates the table.

I provided this same answer in another post as well.

In VSTO2010, the ability to programmatically switch the selected tab was added. Norm Estabrook blogged about it.

this.RibbonUI.ActivateTabMso("TabAddIns");

So while there isn't an event that can be latched onto, at least you can switch the selected tab. For instance, I'm going to use the sheet selection event; when users switch between different sheets of my workbook, I am going to change their selected tab. Each sheet will correspond to a different ribbon tab.

If you pair this with a sheet SelectionChanged event, you could ensure that they are taken back to the appropriate tab (not TabHome) after they change their selected cell.

Just for all of you that have to support Office 2007 also (like me). Here's an (ugly, but working) solution for Office 2007:

1. Open the office application
2. Press ALT and then see the keyboard shortcut for your custom ribbon tab
3. In your code you can now send this keys via the SendKeys.SendWait function

Hope it helps someone. Regards, Jörg

Code:

    public void FocusMyCustomRibbonTab()
    {
        if (IsExcel2007())
        {
            Globals.Ribbons.GetRibbon<MyRibbon>().tabMyRibbonTab.KeyTip = "GGG";

            //Excel 2007: Must send "ALT" key combination to activate tab, here "GGG"
            SendKeys.Send("%");                       
            SendKeys.Send("{G}");                     
            SendKeys.Send("{G}");                     
            SendKeys.Send("{G}");                     
            SendKeys.Send("%");                       
        }
        else
        {
            //Excel 2010 or higher: Build in way to activate tab
            if (this.ribbon.RibbonUI != null)
            {
                this.ribbon.RibbonUI.ActivateTab("MY_RIBBON_TAB_NAME");
            }
        }
    }

    public static bool IsExcel2007()
    {
        return (Globals.ThisAddIn.Application.Version.StartsWith("12"));
    }

I don't think you'll consider this an "answer" but the best thing I could find was someone on MSDN suggesting I assign a keyboard shortcut to the ribbon and then using Application.SendKeys to select it when I assumed the user will need to see the ribbon (on startup after I create the initial table, etc.)

Best of luck to you, and I hope you find a better answer - I will gladly update my application in that case!

Edit: I finally discovered a code.msdn.microsoft.com project that incorporates native C# ribbon manipulation. Use that instead of any VBA business.

Original Post:

Office 2010 allows you to activate a tab using the ribbon extensibility model. However, it doesn't provide any way to know which tab is active or to know if a tab was changed AFAIK. Also, tab activation is not available in 2007. Hopefully these are things they add in the next release.

    public static string GetRibbonName()
    {
        Excel.Workbook wbk = Globals.ThisWorkbook.Application.ActiveWorkbook;
        VBIDE.VBComponent module1 = null; //contains the function I want
        VBIDE.VBComponent module2 = null; //contains helper functions
        object obj = null;
        ThisRibbonCollection rbn = new ThisRibbonCollection();

        module1 = wbk.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);
        module2 = wbk.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);
        module1.CodeModule.AddFromString(Forecast_Toolset.Properties.Settings.Default.Module1);
        module2.CodeModule.AddFromString(Forecast_Toolset.Properties.Settings.Default.Module2);

        obj = wbk.Application.Run("TabName");

        wbk.VBProject.VBComponents.Remove(module1);
        wbk.VBProject.VBComponents.Remove(module2);

        return obj.ToString();

    }