Excel VSTO access sheet name through ThisAddIn StartUp

Question

I am stuck and hoping to get some help here. I have an Excel VSTO add-in with a ribbon that contains various controls. I would like to have all but one control disabled unless a sheet with a specific name is active. How is this accomplished? So far, I figured out how to map the ribbon button to the ThisAddIn startup in order to keep it active, while the other buttons default to disabled:

RibbonButton btx_newECO = Globals.Ribbons.Ribbon1.btn_ECOnumber;
btx_newECO.Enabled = true;

Now, how do I enable the other ribbon buttons when the sheet with a specific name is opened? `

Answer 1

My answer assumes that the Ribbon has been defined at design-time, using the Ribbon Designer. From your code, it appears this is what you're doing (as opposed to using Ribbon XML).

Note a field is declared at the class-level for the Ribbon class and is populated in the ThisAddin_Startup event so that it can be used at anytime during the "life" of the Add-in.

In addition, a SheetActivate event for the application is instantiated. This triggers every time a different worksheet is activated. In the event the name of the worksheet is checked. If it's the right one, the ribbon button is enabled; otherwise the button is disabled.

I set the button to be disabled by default in the Ribbon Designer properties. But you could also disable it in the Ribbon classes Load event, if you prefer.

    public partial class ThisAddIn
{
    Ribbon1 rbn;
    string triggerName = "X";
    private void ThisAddIn_Startup(object sender, System.EventArgs e)
    {
        rbn = Globals.Ribbons.Ribbon1;
        this.Application.SheetActivate += new Excel.AppEvents_SheetActivateEventHandler(Application_SheetActivate);
    }

    void Application_SheetActivate(object Sh)
    {
        Excel.Worksheet wsh = (Excel.Worksheet)Sh;
        if (wsh.Name == triggerName)
        {
            rbn.btnMsg.Enabled = true;
        }
        else { rbn.btnMsg.Enabled = false; }
    }

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

    #region VSTO generated code
}

Answer 2

Okay, Cindy got me going in the right direction. Rather than using a SheetActivateHandler I used a WorkBookOpenHandler. I had no idea I could assign event handlers this way. I totally learned this today. Thanks, Cindy :)

--ThisAddIn---

public partial class ThisAddIn
{
    Ribbon1 rbn;
    string triggerName = "ECO";

    #region StartUp
    private void ThisAddIn_Startup(object sender, System.EventArgs e)
    {
        //detect the sheet name on workbook open event
        rbn = Globals.Ribbons.Ribbon1;
        this.Application.WorkbookOpen+= new Excel.AppEvents_WorkbookOpenEventHandler(Application_WorkbookOpen);
    }

    #endregion

    #region Enable Ribbon Buttons
    void Application_WorkbookOpen(object Sh)
    {
        Excel.Worksheet ws = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet;
        if (ws.Name == triggerName)
        {
            rbn.btn_ECOnumber.Enabled = false;
        } 
    }
    #endregion

---Shutdown and VSTO generated Code here-----