CODEWITHSUNDEEP
c#excelvsto
Patrik
Patrik

Reputation: 413

Excel VSTO Custom task Pane availability

I writting an Excel application-level VSTO. But there is something I do not understand. The custom ribbon tab I created is available at the application level, meaning for all opened workbooks.

I created the ribbon tab using the Ribbon Designer and I call an instance of it through a function setRibbonControlState in the ThisAddIn.cs file. The function is called by the 3 events I handle from the ThisAddIn_startup:

((Excel.AppEvents_Event)Application).NewWorkbook += new Excel.AppEvents_NewWorkbookEventHandler(App_NewWorkbook);
Application.WorkbookBeforeClose += new Excel.AppEvents_WorkbookBeforeCloseEventHandler(App_WorkbookBeforeClose);
Application.WorkbookOpen += new Excel.AppEvents_WorkbookOpenEventHandler(App_NewWorkbook);

At this level, everything works fine.

Then I created 3 User Tab Controls (with a .cs file for each of course) to display in 3 CustomTaskPanes. I created the 3 CustomTaskPanes in the load event handler of the ribbon tab and wrote functions to display the appropriate Task Pane and the appropriate Tab of the inner control. Here is a partial code of the ribbon.cs, showing how I created and display the CustomTaskPane:

private CustomTaskPane tsPane;

    private void tsFinStRibbon_Load(object sender, RibbonUIEventArgs e)
    {
        // Custom task pane
        tsPane = Globals.ThisAddIn.CustomTaskPanes.Add(new tsDataControl(), "Data", Globals.ThisAddIn.Application.ActiveWindow);
        tsPane = Globals.ThisAddIn.CustomTaskPanes.Add(new tsStControl(), "Statements", Globals.ThisAddIn.Application.ActiveWindow);
        tsPane = Globals.ThisAddIn.CustomTaskPanes.Add(new tsPubControl(), "Publishing", Globals.ThisAddIn.Application.ActiveWindow);
        tsPane.DockPosition = Microsoft.Office.Core.MsoCTPDockPosition.msoCTPDockPositionRight;
        tsPane.Visible = false;
    }

    // Données
    private tsDataControl setDataPane()
    {
        tsPane = Globals.ThisAddIn.CustomTaskPanes[0];
        tsDataControl control = tsPane.Control as tsDataControl;

        if (!tsPane.Visible)
        {
            tsPane.Width = 320;
            tsPane.Visible = true;
        }

        return control;
    }

    // Etats
    private tsStControl setStPane()
    {
        tsPane = Globals.ThisAddIn.CustomTaskPanes[1];
        tsStControl control = tsPane.Control as tsStControl;

        if (!tsPane.Visible)
        {
            tsPane.Width = 320;
            tsPane.Visible = true;
        }

        return control;
    }

    // Publications
    private tsPubControl setPubPane()
    {
        tsPane = Globals.ThisAddIn.CustomTaskPanes[2];
        tsPubControl control = tsPane.Control as tsPubControl;

        if (!tsPane.Visible)
        {
            tsPane.Width = 320;
            tsPane.Visible = true;
        }

        return control;
    }

    // Balance générale
    private void tsBtn1A_Click(object sender, RibbonControlEventArgs e)
    {
        if(Globals.ThisAddIn.CustomTaskPanes.Count != 0)
        {
            tsDataControl control = setDataPane();
            control.selectTab(0);
        }
    }

    // balance tiers
    private void tsBtn1B_Click(object sender, RibbonControlEventArgs e)
    {
        if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
        {
            tsDataControl control = setDataPane();
            control.selectTab(1);
        }
    }

    // Calcul des états
    private void tsBtn2A_Click(object sender, RibbonControlEventArgs e)
    {
        if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
        {
            tsStControl control = setStPane();
            RibbonSplitButton btn = (RibbonSplitButton)sender as RibbonSplitButton;
            string btnId = btn.Name;
            control.selectTab(0, btnId);
        }
    }

    // -- Calcul des états avec notes annexes
    private void tsBtn2A1_Click(object sender, RibbonControlEventArgs e)
    {
        if(Globals.ThisAddIn.CustomTaskPanes.Count != 0)
        {
            tsStControl control = setStPane();
            RibbonButton btn = (RibbonButton)sender as RibbonButton;
            string btnId = btn.Name;
            control.selectTab(0, btnId);
        }
    }

    // -- Calcul des états sans notes annexes
    private void tsBtn2A2_Click(object sender, RibbonControlEventArgs e)
    {
        if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
        {
            tsStControl control = setStPane();
            RibbonButton btn = (RibbonButton)sender as RibbonButton;
            string btnId = btn.Name;
            control.selectTab(0, btnId);
        }
    }

    // Bilan
    private void tsBtn2B_Click(object sender, RibbonControlEventArgs e)
    {
        if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
        {
            tsStControl control = setStPane();
            RibbonButton btn = (RibbonButton)sender as RibbonButton;
            string btnId = btn.Name;
            control.selectTab(1, btnId);
        }
    }

    // -- Bilan actif
    private void tsBtn3A_Click(object sender, RibbonControlEventArgs e)
    {
        if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
        {
            tsStControl control = setStPane();
            RibbonSplitButton btn = (RibbonSplitButton)sender as RibbonSplitButton;
            string btnId = btn.Name;
            control.selectTab(1, btnId);
        }
    }

    // -- Bilan Passif
    private void tsBtn3B_Click(object sender, RibbonControlEventArgs e)
    {
        if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
        {
            tsStControl control = setStPane();
            RibbonSplitButton btn = (RibbonSplitButton)sender as RibbonSplitButton;
            string btnId = btn.Name;
            control.selectTab(1, btnId);
        }
    }

When I open Excel, I can access the appropriate task pane and tab in the inner control only from the initial spreadsheet. Any further spreadsheet I open or create provides the ribbon tab but when I click on any of its button, the custom task panes do not show up.

I tried to created the tasks panes from the ThisAddIn.cs file, and it effectively creates them, but they react only when I click on the ribbon from the initial spreadsheet.

In my reading, I could not find a way to efficiently attach the ribbon to the tasks panes to make it behave correctly.

Does anyone have an idea how to fix it?

Thank.

EDIT

Here is the complete ThisAddIn.cs code:

public partial class ThisAddIn
{
    private void ThisAddIn_Startup(object sender, System.EventArgs e)
    {
        // Events handlers
        ((Excel.AppEvents_Event)Application).NewWorkbook += new Excel.AppEvents_NewWorkbookEventHandler(App_NewWorkbook);
        Application.WorkbookBeforeClose += new Excel.AppEvents_WorkbookBeforeCloseEventHandler(App_WorkbookBeforeClose);
        Application.WorkbookOpen += new Excel.AppEvents_WorkbookOpenEventHandler(App_NewWorkbook);
    }

    private void setRibbonControlState(ref bool isEnabled)
    {
        int workbookCount = Application.Windows.Count;
        if (workbookCount > 1 && !isEnabled)
            return;

        tsFinStRibbon ribbon = Globals.Ribbons.tsFinStRibbon;
        int tabCount = ribbon.Tabs.Count;

        for (int i = 0; i < tabCount; i++)
        {
            RibbonTab tab = ribbon.Tabs[i];
            int grpCount = tab.Groups.Count;

            for (int j = 0; j < grpCount; j++)
            {
                RibbonGroup grp = tab.Groups[j];
                int itCount = grp.Items.Count;

                for (int k = 0; k < itCount; k++)
                {
                    grp.Items[k].Enabled = isEnabled;
                }
            }
        }

        if (!isEnabled)
        {
            int paneCount = CustomTaskPanes.Count;
            for (int i = 0; i < paneCount; i++)
                CustomTaskPanes[i].Visible = false;
        }
    }

    private void App_NewWorkbook(Excel.Workbook Wb)
    {
        // Set the ribbon
        bool isEnabled = true;
        setRibbonControlState(ref isEnabled);
    }

    private void App_WorkbookBeforeClose(Excel.Workbook Wb, ref bool Cancel)
    {
        bool isEnabled = (Cancel) ? false : true;
        setRibbonControlState(ref isEnabled);
    }

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

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

    #endregion
}

Here is the complete tsFinRibbon.cs code:

public partial class tsFinStRibbon
{
    //static private Dictionary<string, CustomTaskPane> tsDPanes = new Dictionary<string, CustomTaskPane>();
    private CustomTaskPane tsPane;

    private void tsFinStRibbon_Load(object sender, RibbonUIEventArgs e)
    {
        // Custom task pane
        Globals.ThisAddIn.CustomTaskPanes.Add(new tsDataControl(), "Data");
        Globals.ThisAddIn.CustomTaskPanes.Add(new tsStControl(), "Statements");
        Globals.ThisAddIn.CustomTaskPanes.Add(new tsPubControl(), "Publishing");
    }

    // Data task pane call
    private tsDataControl setDataPane()
    {
        tsPane = Globals.ThisAddIn.CustomTaskPanes[0];
        tsDataControl control = tsPane.Control as tsDataControl;

        if (!tsPane.Visible)
        {
            tsPane.Width = 320;
            tsPane.Visible = true;
        }

        return control;
    }

    // Statement task pane call
    private tsStControl setStPane()
    {
        tsPane = Globals.ThisAddIn.CustomTaskPanes[1];
        tsStControl control = tsPane.Control as tsStControl;

        if (!tsPane.Visible)
        {
            tsPane.Width = 320;
            tsPane.Visible = true;
        }

        return control;
    }

    // Publications task pane call
    private tsPubControl setPubPane()
    {
        tsPane = Globals.ThisAddIn.CustomTaskPanes[2];
        tsPubControl control = tsPane.Control as tsPubControl;

        if (!tsPane.Visible)
        {
            tsPane.Width = 320;
            tsPane.Visible = true;
        }

        return control;
    }

    // Balance générale
    private void tsBtn1A_Click(object sender, RibbonControlEventArgs e)
    {
        if(Globals.ThisAddIn.CustomTaskPanes.Count != 0)
        {
            tsDataControl control = setDataPane();
            control.selectTab(0);
        }
    }

    // balance tiers
    private void tsBtn1B_Click(object sender, RibbonControlEventArgs e)
    {
        if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
        {
            tsDataControl control = setDataPane();
            control.selectTab(1);
        }
    }

    // Calcul des états
    private void tsBtn2A_Click(object sender, RibbonControlEventArgs e)
    {
        if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
        {
            tsStControl control = setStPane();
            RibbonSplitButton btn = (RibbonSplitButton)sender as RibbonSplitButton;
            string btnId = btn.Name;
            control.selectTab(0, btnId);
        }
    }

    // -- Calcul des états avec notes annexes
    private void tsBtn2A1_Click(object sender, RibbonControlEventArgs e)
    {
        if(Globals.ThisAddIn.CustomTaskPanes.Count != 0)
        {
            tsStControl control = setStPane();
            RibbonButton btn = (RibbonButton)sender as RibbonButton;
            string btnId = btn.Name;
            control.selectTab(0, btnId);
        }
    }

    // -- Calcul des états sans notes annexes
    private void tsBtn2A2_Click(object sender, RibbonControlEventArgs e)
    {
        if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
        {
            tsStControl control = setStPane();
            RibbonButton btn = (RibbonButton)sender as RibbonButton;
            string btnId = btn.Name;
            control.selectTab(0, btnId);
        }
    }

    // Bilan
    private void tsBtn2B_Click(object sender, RibbonControlEventArgs e)
    {
        if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
        {
            tsStControl control = setStPane();
            RibbonButton btn = (RibbonButton)sender as RibbonButton;
            string btnId = btn.Name;
            control.selectTab(1, btnId);
        }
    }

    // -- Bilan actif
    private void tsBtn3A_Click(object sender, RibbonControlEventArgs e)
    {
        if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
        {
            tsStControl control = setStPane();
            RibbonSplitButton btn = (RibbonSplitButton)sender as RibbonSplitButton;
            string btnId = btn.Name;
            control.selectTab(1, btnId);
        }
    }

    // -- Bilan Passif
    private void tsBtn3B_Click(object sender, RibbonControlEventArgs e)
    {
        if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
        {
            tsStControl control = setStPane();
            RibbonSplitButton btn = (RibbonSplitButton)sender as RibbonSplitButton;
            string btnId = btn.Name;
            control.selectTab(1, btnId);
        }
    }

    // -- Bilan N-1
    private void tsBtn3C_Click(object sender, RibbonControlEventArgs e)
    {
        if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
        {
            tsStControl control = setStPane();
            RibbonSplitButton btn = (RibbonSplitButton)sender as RibbonSplitButton;
            string btnId = btn.Name;
            control.selectTab(1, btnId);
        }
    }

    // -- Haut du bilan
    private void tsBtn3D_Click(object sender, RibbonControlEventArgs e)
    {
        if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
        {
            tsStControl control = setStPane();
            RibbonButton btn = (RibbonButton)sender as RibbonButton;
            string btnId = btn.Name;
            control.selectTab(1, btnId);
        }
    }

    // -- Bas du bilan
    private void tsBtn3E_Click(object sender, RibbonControlEventArgs e)
    {
        if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
        {
            tsStControl control = setStPane();
            RibbonButton btn = (RibbonButton)sender as RibbonButton;
            string btnId = btn.Name;
            control.selectTab(1, btnId);
        }
    }

    // Compte de résultat
    private void tsBtn2C_Click(object sender, RibbonControlEventArgs e)
    {
        if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
        {
            tsStControl control = setStPane();
            RibbonButton btn = (RibbonButton)sender as RibbonButton;
            string btnId = btn.Name;
            control.selectTab(2, btnId);
        }
    }

    // -- Compte de résultat charges
    private void tsBtn4A_Click(object sender, RibbonControlEventArgs e)
    {
        if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
        {
            tsStControl control = setStPane();
            RibbonSplitButton btn = (RibbonSplitButton)sender as RibbonSplitButton;
            string btnId = btn.Name;
            control.selectTab(2, btnId);
        }
    }

    private void tsBtn4B_Click(object sender, RibbonControlEventArgs e)
    {
        if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
        {
            tsStControl control = setStPane();
            RibbonSplitButton btn = (RibbonSplitButton)sender as RibbonSplitButton;
            string btnId = btn.Name;
            control.selectTab(2, btnId);
        }
    }

    private void tsBtn4C_Click(object sender, RibbonControlEventArgs e)
    {
        if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
        {
            tsStControl control = setStPane();
            RibbonSplitButton btn = (RibbonSplitButton)sender as RibbonSplitButton;
            string btnId = btn.Name;
            control.selectTab(2, btnId);
        }
    }

    private void tsBtn4D_Click(object sender, RibbonControlEventArgs e)
    {
        if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
        {
            tsStControl control = setStPane();
            RibbonSplitButton btn = (RibbonSplitButton)sender as RibbonSplitButton;
            string btnId = btn.Name;
            control.selectTab(2, btnId);
        }
    }

    // Flux de trésorerie
    private void tsBtn2D_Click(object sender, RibbonControlEventArgs e)
    {
        if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
        {
            tsStControl control = setStPane();
            RibbonButton btn = (RibbonButton)sender as RibbonButton;
            string btnId = btn.Name;
            control.selectTab(3, btnId);
        }
    }

    private void tsBtn5A_Click(object sender, RibbonControlEventArgs e)
    {
        if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
        {
            tsStControl control = setStPane();
            RibbonSplitButton btn = (RibbonSplitButton)sender as RibbonSplitButton;
            string btnId = btn.Name;
            control.selectTab(3, btnId);
        }
    }

    private void tsBtn5B_Click(object sender, RibbonControlEventArgs e)
    {
        if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
        {
            tsStControl control = setStPane();
            RibbonButton btn = (RibbonButton)sender as RibbonButton;
            string btnId = btn.Name;
            control.selectTab(3, btnId);
        }
    }

    private void tsBtn5C_Click(object sender, RibbonControlEventArgs e)
    {
        if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
        {
            tsStControl control = setStPane();
            RibbonSplitButton btn = (RibbonSplitButton)sender as RibbonSplitButton;
            string btnId = btn.Name;
            control.selectTab(3, btnId);
        }
    }

    private void tsBtn5D_Click(object sender, RibbonControlEventArgs e)
    {
        if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
        {
            tsStControl control = setStPane();
            RibbonSplitButton btn = (RibbonSplitButton)sender as RibbonSplitButton;
            string btnId = btn.Name;
            control.selectTab(3, btnId);
        }
    }

    // Annexes
    private void tsBtn6A_Click(object sender, RibbonControlEventArgs e)
    {
        if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
        {
            tsStControl control = setStPane();
            RibbonSplitButton btn = (RibbonSplitButton)sender as RibbonSplitButton;
            string btnId = btn.Name;
            control.selectTab(4, btnId);
        }
    }

    private void tsBtn7A_Click(object sender, RibbonControlEventArgs e)
    {
        if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
        {
            tsPubControl control = setPubPane();
            control.selectTab(0);
        }
    }

    private void tsBtn7B_Click(object sender, RibbonControlEventArgs e)
    {
        if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
        {
            tsPubControl control = setPubPane();
            control.selectTab(1);
        }
    }

    private void tsBtn7C_Click(object sender, RibbonControlEventArgs e)
    {
        if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
        {
            tsPubControl control = setPubPane();
            control.selectTab(2);
        }
    }
}

I changed a bit the load event handler of the custom ribbon tab. The use of the tspane property was irrelevant. The load event handler of the custom ribbon tab creates three custom tasks panes. Each of them are related to a specific group of ribbon buttons. When a button in a group is clicked, the appropriate task pane is called and the appropriate tabControl in the task pane is activated.

As I said before, when I open Excel, only the initial workbook return the custom task pane when I click on a ribbon button.

I also noticed that when I open Excel, create/open a second workbook and click on a ribbon button of the custom ribbon tab, the task pane that is visible on the initial workbook reacts and activates the correct tab or task pane.

Upvotes: 1

Views: 1573

Answers (1)

Patrik
Patrik

Reputation: 413

I figured it out finally. The Custom Task Panes must be created for each window it needs to appear on. As I thought the Custom Task Panes was bound to the application and not any specific Workbook, I just created the 3 of them only once.

So, I created a Dictionary to keep track of the Task Panes I creates and its position in the CustomTaskPaneCollection so I can recall / or create (if necessary) any Task Pane when I switch Excel windows. Here are the key codes:

    private CustomTaskPane tsPane;
    private int Wn;
    private Dictionary<string, int> dict;

    private void tsFinStRibbon_Load(object sender, RibbonUIEventArgs e)
    {
        // Create a dictionary of indexes
        dict = new Dictionary<string, int>();
    }
    // Données
    private tsDataControl setDataPane()
    {
        // Initialize the window ID and pane name
        Wn = Globals.ThisAddIn.Application.Hwnd;
        string paneName = "Data" + Wn.ToString();

        // Check if the CTP already exists and create it otherwise
        if (!dict.ContainsKey(paneName))
        {
            tsPane = Globals.ThisAddIn.CustomTaskPanes.Add(new tsDataControl(), paneName);
            dict.Add(paneName, Globals.ThisAddIn.CustomTaskPanes.Count - 1);
        }
        else
        {
            tsPane = Globals.ThisAddIn.CustomTaskPanes[dict[paneName]];
        }

        // Get the tab control
        tsDataControl control = tsPane.Control as tsDataControl;

        if (!tsPane.Visible)
        {
            tsPane.Width = 320;
            tsPane.Visible = true;
        }

        return control;
    }

    // Etats
    private tsStControl setStPane()
    {
        // Initialize the window ID and pane name
        Wn = Globals.ThisAddIn.Application.Hwnd;
        string paneName = "Statement" + Wn.ToString();

        // Check if the CTP already exists and create it otherwise
        if (!dict.ContainsKey(paneName))
        {
            tsPane = Globals.ThisAddIn.CustomTaskPanes.Add(new tsStControl(), "Statement");
            dict.Add(paneName, Globals.ThisAddIn.CustomTaskPanes.Count - 1);
        }
        else
        {
            tsPane = Globals.ThisAddIn.CustomTaskPanes[dict[paneName]];
        }

        // Get the tab control
        tsStControl control = tsPane.Control as tsStControl;

        // Display the pane
        if (!tsPane.Visible)
        {
            tsPane.Width = 320;
            tsPane.Visible = true;
        }

        return control;
    }

    // Publications
    private tsPubControl setPubPane()
    {
        // Initialize the window ID and pane name
        Wn = Globals.ThisAddIn.Application.Hwnd;
        string paneName = "Publishing" + Wn.ToString();

        // Check if the CTP already exists and create it otherwise
        if (!dict.ContainsKey(paneName))
        {
            tsPane = Globals.ThisAddIn.CustomTaskPanes.Add(new tsPubControl(), paneName);
            dict.Add(paneName, Globals.ThisAddIn.CustomTaskPanes.Count - 1);
        }
        else
        {
            tsPane = Globals.ThisAddIn.CustomTaskPanes[dict[paneName]];
        }

        // Get the tab control  
        tsPubControl control = tsPane.Control as tsPubControl;

        // Display the pane
        if (!tsPane.Visible)
        {
            tsPane.Width = 320;
            tsPane.Visible = true;
        }

        return control;
    }

Thanks!

Upvotes: 1

Related Questions