Changing the VSTO tab's visible property throu

Question

I have written the Ribbon.xml file for an Excel VSTO project. The tab element looks like this:

<tab id="myId" idMso="TabAddIns" label="My Tab" visible="false">

When a workbook is opened, I want the tab to be hidden by default, which is accomplished through the visible property being set to false. Next, I want to change the visible property to true in the Workbook_Open event. This is where I'm stuck. I wouldn't think this would be hard, but I've spent a couple hours googling for the answer. It seems that most examples 1) toggle a tab's visibility through a button callback, which is not what I want to do, or 2) are able to access the ribbon's properties, which I have not been able to replicate so far (although, most of these resources are old, so I'm thinking that MS moved these properties around since then).

Does anyone know how to easily change the visible property to true so that the tab is displayed?

Thank you!

UPDATED WITH ADDITIONAL INFORMATION:

ThisAddIn.cs

namespace Doodles_Reporting
{
    public partial class ThisAddIn
    {
        public RibbonApi ribbonApi;


        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
        }

        private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
        {
        }

        protected override Microsoft.Office.Core.IRibbonExtensibility CreateRibbonExtensibilityObject()
        {
            return new Ribbon();
        }

        void Application_WorkbookOpen(Excel.Workbook Wb)
        {

            //first, check if there is an application/process for each workbook
            Excel.Workbooks books = Globals.ThisAddIn.Application.Workbooks;
            if (books.Count > 1)
            {
                try
                {
                    //close workbook that was just opened and then reopen it with new process/application.
                    string filePath = Wb.FullName;
                    Wb.Close();
                    Excel.Application excelApp = new Excel.Application();
                    excelApp.Visible = true;
                    excelApp.DisplayFullScreen = true;
                    excelApp.Workbooks.Open(filePath);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK);
                }
            }
            else
            {
                //second, check if the workbook is a Doodles workbook
                try
                {
                    DocumentProperties props = (DocumentProperties)Wb.CustomDocumentProperties;
                    var selectedTable = props["selectedTable"].Value;
                    configureDoodles();
                }
                catch (Exception)
                {
                 //THIS IS WHERE I WANT TO SET THE RIBBON VISIBILITY TO FALSE
                }
            }   
        }

        private void configureDoodles()
        {
            RibbonApi.app = Globals.ThisAddIn.Application;
            RibbonApi.wBookPropertiesConfig = new WorkbookPropertiesConfig(RibbonApi.app.ActiveWorkbook);
            RibbonApi.presenter = new ExcelPresenter(RibbonApi.app.ActiveWorkbook);
            ribbonApi = new RibbonApi();
        }

        #region VSTO generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.Startup += new System.EventHandler(ThisAddIn_Startup);
            this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
            this.Application.WorkbookOpen += new Excel.AppEvents_WorkbookOpenEventHandler(Application_WorkbookOpen);
        }

        #endregion
    }
}

Ribbon.cs

namespace Doodles_Reporting
{
    [ComVisible(true)]
    public class Ribbon : Office.IRibbonExtensibility
    {
        private Office.IRibbonUI ribbon;

        public Ribbon()
        {
        }

        #region IRibbonExtensibility Members

        public string GetCustomUI(string ribbonID)
        {
            return GetResourceText("Doodles_Reporting.Ribbon.xml");
        }

        #endregion

        #region Ribbon Callbacks
        //Create callback methods here. For more information about adding callback methods, visit http://go.microsoft.com/fwlink/?LinkID=271226

        public void Ribbon_Load(Office.IRibbonUI ribbonUI)
        {
            this.ribbon = ribbonUI;
        }

        public bool toggleVisibility(Office.IRibbonControl control)
        {
            return (control.Id == "TabAddIns") ? true : false;
        }

        public void onSomeEvent()
        {
            this.ribbon.InvalidateControl("TabAddIns");
        }

        public void SignIn(Office.IRibbonControl ribbonUI)
        {
            Globals.ThisAddIn.ribbonApi.signIn();
        }

        public void SqlCreatorFormLoad(Office.IRibbonControl ribbonUI)
        {
            Globals.ThisAddIn.ribbonApi.showSqlCreator();
        }

        public void refreshData(Office.IRibbonControl ribbonUI)
        {
            Globals.ThisAddIn.ribbonApi.refreshData();
        }

        public void drilldownSelectionLoad(Office.IRibbonControl ribbonUI)
        {
            Globals.ThisAddIn.ribbonApi.setDrilldownColumns();
        }

        public void Drilldown(Office.IRibbonControl ribbonUI)
        {
            Globals.ThisAddIn.ribbonApi.drilldown();
        }

        public void editProperties(Office.IRibbonControl ribbonUI)
        {

        }

        #endregion

        #region Helpers

        private static string GetResourceText(string resourceName)
        {
            Assembly asm = Assembly.GetExecutingAssembly();
            string[] resourceNames = asm.GetManifestResourceNames();
            for (int i = 0; i < resourceNames.Length; ++i)
            {
                if (string.Compare(resourceName, resourceNames[i], StringComparison.OrdinalIgnoreCase) == 0)
                {
                    using (StreamReader resourceReader = new StreamReader(asm.GetManifestResourceStream(resourceNames[i])))
                    {
                        if (resourceReader != null)
                        {
                            return resourceReader.ReadToEnd();
                        }
                    }
                }
            }
            return null;
        }

        #endregion
    }
}

Answer 1

You've already got all the pieces.

In ThisAddin, you're overriding CreateRibbonExtensibilityObject. Instead of returning a new Ribbon, return an object that you keep hold of.

public partial class ThisAddIn
{
    private readonly Ribbon _ribbon = new Ribbon();

    protected override Microsoft.Office.Core.IRibbonExtensibility CreateRibbonExtensibilityObject()
    {
        return this._ribbon;
    }
}

Then, either add a public Invalidate() method(s) to that object, or make that object's IRibbonUI field public and call Invalidate/InvalidateControl on that. I would probably do the former.

[ComVisible(true)]
public class Ribbon : Office.IRibbonExtensibility
{
    private Office.IRibbonUI _ribbonUI;

    public void Invalidate()
    {
        this._ribbonUI.Invalidate();
    }

    public void InvalidatePlayButton()
    {
        this._ribbonUI.Invalidate("PlayButton");
    }
}

Then call those public methods from Application_WorkbookOpen.

public partial class ThisAddIn
{
    void Application_WorkbookOpen(Excel.Workbook Wb)
    {
        this._ribbon.Invalidate();
        this._ribbon.InvalidatePlayButton();
    }
}

Note that you can combine these objects into a single object, if you like. Just have ThisAddin implement Microsoft.Office.Core.IRibbonExtensibility and return this from CreateRibbonExtensibilityObject.

However, there are some advantages to creating a separate object. For example, it becomes easy to encapsulate your ribbon in more robust public methods and properties. For example:

public class Ribbon : Office.IRibbonExtensibility
{
    private Office.IRibbonUI _ribbonUI;

    private bool _isPlayButtonVisible = true;

    // here's a property that makes it easy to update the visibility of the play button
    public bool IsPlayButtonVisible
    {
        get { return this._isPlayButtonVisible; }
        set
        {
            if (this._isPlayButtonVisible != value)
            {
                this._isPlayButtonVisible = value;
                this._ribbonUI.InvalidateControl("PlayButton");
            }
        }
    }

    // here's the callback that Excel will call
    public bool GetIsPlayButtonVisible(IRibbonControl control)
    {
        return this.IsPlayButtonVisible
    }
}

Answer 2

So, I found out that using the Ribbon Designer makes this far simpler. After I create a new Ribbon Designer class, the class is added to the object model under Globals.Ribbons. All that means that hiding the tab becomes as simple as I thought it should be with one line of code:

Globals.Ribbons.RibbonVisual.Doodles.Visible = false;

Where RibbonVisual is the name of the Ribbon Designer class I created and Doodles is the name of the tab.

I'll be using the Ribbon Designer from now on lol :P.

Answer 3

The ribbon is a funny beast. It's specifically designed to disallow you to get direct access to any of its elements and to disallow you to directly manipulate them. Instead, everything is accomplished via callbacks. I don't have a lot of experience with VSTO, but I can explain what you would do in C# or C++ without VSTO, and I believe you can fill in the gaps.

1. Set an onLoad callback in your ribbon XML. Excel will call this method (via IDispatch) when the ribbon is first loaded.

   <customUI ... onLoad="OnRibbonLoaded">
   

2. Implement your onLoad callback, which should store the IRibbonUI reference that's provided.

   public void OnRibbonLoaded(IRibbonUI ribbon)
   {
       this.ribbon = ribbon;
   }
   

3. For the properties you want to dynamically control, define a callback in the ribbon XML.

   <tab ... getVisible="GetVisible">
   

4. Implement your visibility callback. If multiple parts of your ribbon use the same callback, the IRibbonControl instance passed in to this method can be used to determine which tab/group/control is being queried.

   public bool GetVisible(IRibbonControl control)
   {
       // here is where you should determine if your tab/group/control should be visible,
       return (some condition) ? true : false;
   }
   

5. Whenever you decide that you want to update the visibility, tell Excel to re-query your control's properties (i.e. call your callbacks) by using the IRibbonUI reference.

   void OnSomeEvent()
   {
       // you can tell Excel to update the entire ribbon
       this.ribbon.Invalidate();
   
       // or you can tell Excel to update a single tab/group/control
       this.ribbon.InvalidateControl("my_id");
   }
   

Answer 4

For anyone wondering how to do this with a ribbon from designer:

If you tried this:

Globals.Ribbons.MyRibbon.MyRibbonTab.Visible = false;

And nothing happened, try setting all your RibbonGroups to Visible = false. When all RibbonGroups inside your Ribbon are Not visible, the whole ribbon will automaticly set to Visible = false.

private void SetRibbonVisibility(bool visible)
    {
        foreach (var ribbonGroup in Globals.Ribbons.Ribbon.MyRibbonTab.Groups)
        {
            ribbonGroup.Visible = visible;
        }
    }

Yes, VSTO is a joy to work with.