Select VSTO Custom Ribbon in Excel

Question

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 ribbon to be visible after the action that creates the table.

Answer 1

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.

Answer 2

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"));
    }

Answer 3

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!

Answer 4

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.

But anyway some guys found a way to access the IAccessible objects in the ribbon. You can pretty much do anything you want with the ribbon using these methods. I've tried porting it to C#, but I encountered, and heard others also encountered, memory leak issues with the AccessibleChildren pinvoke function, so if anyone finds a way to do this natively in C#, please share.

As a workaround, I modified the VBA functions contained in the .dotm file from their website, placed the VBA code in my app's property settings and create and destroy VBA modules in my workbook whenever I need to find out the current tab name.

    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();

    }