none
Custom Task Pane in mutiple instances of Excel 2016 RRS feed

  • Question

  • I am trying to write code for a custom taskpane as outlined in 

    https://msdn.microsoft.com/en-us/library/bb608590.aspx

    All works well and I can open and close the taskpane.  But, if I open a new instance of Excel - so say the first instance is Excel1 and the second is Excel2 each with their own workbooks open, the taskpane will never appear in Excel2; the toggle button will open and close the taskpane in Excel1 and use the data from Excel1 one as well...

    I am looking for any ideas as to how to associate the custom taskpane with a second or more instances of Excel. 

    Friday, July 7, 2017 12:31 PM

All replies

  • Hello,

    You could create a new dictionary used to store the Workbook and its related taskpane in the WorkbookActivate event.

    Please create a new button or togglebutton and then test the code below:

         public CustomTaskPane taskPane;
            public Dictionary<string, CustomTaskPane> WbCtp = new Dictionary<string, CustomTaskPane>();
    
            private void ThisAddIn_Startup(object sender, System.EventArgs e)
            {
                this.Application.WorkbookActivate += Application_WorkbookActivate;
           
            }
    
            private void Application_WorkbookActivate(Excel.Workbook Wb)
            {
                    var wbCtp = WbCtp.Where(wb=>wb.Key==Wb.FullName).FirstOrDefault().Value;
                    if (wbCtp == null)
                    {
                        Globals.Ribbons.Ribbon.toggleButton1.Checked = false;
                        taskPane = Globals.ThisAddIn.CustomTaskPanes.Add(new WinForm(), "My task pane");              
                        WbCtp.Add(Wb.FullName, taskPane);
                    }
                    else
                    {
                       taskPane = wbCtp;
                    }
            }
     
            private void toggleButton1_Click(object sender, RibbonControlEventArgs e)
            {
                var taskPane = Globals.ThisAddIn.taskPane;
                if (taskPane != null)
                {
                    taskPane.Visible = !taskPane.Visible;
                }          
            }

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 10, 2017 9:04 AM
    Moderator
  • Thanks for the suggestion!

    Sorry for the late reply - haven't had a chance to try this until now.

    I am writing this in VB.NET and your code appears to be in C.

    Also, I am using VS2013 and the task panes are listed at User Control instead of Task Panes - I am wondering if newer versions of VS have an actual Task Pane object...

    Thanks again,

    Bob

    Sunday, August 6, 2017 4:04 PM
  • Hello,

    There is no task pane object in VS. To add task Pane, we could add User Control.

    Please create a new add-in. Add a usercontrol named as UserControl1. Add a ribbon named as Ribbon1 and add a togglebutton at the ribbon.

    in ThisAddIn.vb

    Public Class ThisAddIn
        Public taskPane As Microsoft.Office.Tools.CustomTaskPane
        Public WbCtp As New Dictionary(Of String, Microsoft.Office.Tools.CustomTaskPane)()
        Private Sub ThisAddIn_Startup() Handles Me.Startup
            AddHandler Me.Application.WorkbookActivate, AddressOf Application_WorkbookActivate
        End Sub
        Private Sub Application_WorkbookActivate(Wb As Excel.Workbook)
            Dim ctp
            ctp = WbCtp.Where(Function(x) x.Key = Wb.FullName).FirstOrDefault().Value
            If ctp Is Nothing Then
                Globals.Ribbons.Ribbon1.ToggleButton1.Checked = False
                taskPane = Globals.ThisAddIn.CustomTaskPanes.Add(New UserControl1(), "My task pane")
                WbCtp.Add(Wb.FullName, taskPane)
            Else
                taskPane = ctp
            End If
        End Sub
        Private S
    in Ribbon1.vb
        Private Sub ToggleButton1_Click(sender As Object, e As RibbonControlEventArgs) Handles ToggleButton1.Click
            Dim taskPane = Globals.ThisAddIn.taskPane
            If taskPane IsNot Nothing Then
                taskPane.Visible = Not taskPane.Visible
            End If

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, August 8, 2017 8:37 AM
    Moderator
  • hello,

    how we can use this addin class to declare multi UserControl, UserControl1(), UserControl2()…..

    thx

    Thursday, January 31, 2019 12:37 PM
  • Hello, 

    I tested your code. it works well but have some problem.

    var wbCtp = WbCtp.Where(wb=>wb.Key==Wb.FullName).FirstOrDefault().Value;

    ==> you use 'Wb.FullName' for distinguish key.

    if I close workbook and reopen it,

    wbCtp (Workbook Customtaskpane) lost it's dock window.

    but still is not null. so it makes 'System.Runtime.InteropServices.COMException' error.

    can I use another unique key of Wb?

    or How can I prevent this COMException error?

    Regards,  JHPark

    Wednesday, August 21, 2019 3:13 AM
  • I solved this problem with Windows Handle 

                     

    public partial class CTaskPane
        {
            public int Hwnd = 0;

            public CustomTaskPane oCTP_CmdTerminal;   //' Custom Task Pane 변수
            public UC_CmdTerminal oMyCtrl_CmdTerminal = null;        //' CmdTerminal를 담을 변수
           
            public CTaskPane(int Hwnd)
            {
                this.Hwnd = Hwnd;

                oCTP_CmdTerminal = null;   //' Custom Task Pane 변수
                createCTP_CmdTerminal();
            }

        }

            public CTaskPane TaskPane = null;
            public Dictionary<string, CTaskPane> WbTP =  new Dictionary<string, CTaskPane>();


           void Application_WorkbookActivate(Excel.Workbook Wb)
            {

                var vTP = WbTP.Where(wb => wb.Key == Wb.FullName).FirstOrDefault().Value;

                    if (vTP == null)
                    {
                        TaskPane = new CTaskPane(this.Application.Hwnd);
                        WbTP.Add(Wb.FullName, TaskPane);
                    }
                    else if (this.Application.Hwnd != vTP.Hwnd)
                    {
                        WbTP.Remove(Wb.FullName);

                        TaskPane = new CTaskPane(this.Application.Hwnd);
                        WbTP.Add(Wb.FullName, TaskPane);
                    }
                    else
                    {
                        TaskPane = vTP;
                    }

            }




    Wednesday, August 21, 2019 7:58 AM
  • Hi,

    could you be post full working example for VB.NET VS 2019, please?

    Thank you

    Milan

    Monday, October 19, 2020 7:44 AM