none
Suppress a built in task pane - (pivot table) RRS feed

  • Question

  • Hi

    I would like to suppress the built in pivot table field list task pane so that it does not automatically appear when the user selects a cell inside a pivot table.

    I am building some functionality that will provide an alternate user interface to add data to the pivot table.

    Any thoughts on how to suppress the built in pane would be appreciated.

     


    Andrew Wiles - www.it-workplace.com - MDX made simple
    Tuesday, July 26, 2011 3:01 PM

Answers

  • Hi Andres,

    For your first requirement: suppress the built-in pivottable field list, please follow the steps below:

    1. Create an Excel add-in
    2. Add a Ribbon XML item
    3. Code the XML file like:

    <?xml version="1.0" encoding="UTF-8"?>
    <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="Ribbon_Load">
     <commands>
      <command idMso ="PivotFieldListShowHide"
           enabled ="false"/>  
     </commands> 
    </customUI>
    
    


    In this way, you can disable the "Field List" togglebutton so that suppress the task pane.

    <<In reality what I need is an event that fires when a Pivot table becomes active which I could then use to make my customer task pane visible and hide the standard task pane>>

    For your last description of your intention, I would recommend you to have a look at this document:

    http://msdn.microsoft.com/en-us/library/bb964683.aspx#Y13489

    This document demonstrate how to use custom a togglebutton to hide and unhide your custom task pane.

    I have done a little bit of modifications to satisfy your requirement:

    1. Code the ribbon XML file like below (the custom ribbon in my test solution named "MyRibbon1")

    <?xml version="1.0" encoding="UTF-8"?>
    <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="Ribbon_Load">
     <commands>
      <command idMso ="PivotFieldListShowHide"
           enabled ="false"/>  
     </commands>
     <ribbon>
      <tabs >
       <tab idMso ="TabInsert">
        <group id ="group1" label ="MyGroup">
         <toggleButton id ="togglebutton1"
                 label ="Custom Task Pane"
                 onAction ="togglebutton1_click"
                 getPressed ="togglebutton1_pressed"/>
        </group>
       </tab>
      </tabs>  
     </ribbon>
    </customUI>
    


    2. In MyRibbon1.cs, code the Callbacks region like this:

        #region Ribbon Callbacks
        //Create callback methods here. For more information about adding callback methods, select the Ribbon XML item in Solution Explorer and then press F1
        public void refresh()
        {
          ribbon.InvalidateControl("togglebutton1");
        }
        public void Ribbon_Load(Office.IRibbonUI ribbonUI)
        {
          this.ribbon = ribbonUI;
        } 
        public void togglebutton1_click(Office.IRibbonControl control, bool isPressed)
        {
          Globals.ThisAddIn.pane.Visible = isPressed;
          Globals.ThisAddIn.Application.ActiveWorkbook.ShowPivotTableFieldList = !isPressed;
        }
        public bool togglebutton1_pressed(Office.IRibbonControl control)
        {
          return Globals.ThisAddIn.pane.Visible;
        }
        #endregion
    


    3. In ThisAddIn.cs file:

        private MyRibbon1 ribbon; 
    
        public Microsoft.Office.Tools.CustomTaskPane pane = null;
        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
          Button button1 = new Button (); 
          UserControl uControl = new UserControl ();
          uControl.Controls.Add(button1);
          pane = CustomTaskPanes.Add(uControl, "MyPane");
          pane.Visible = true;
          pane.VisibleChanged += new EventHandler(pane_VisibleChanged);    
        }
    
        void pane_VisibleChanged(object sender, EventArgs e)
        {
          ribbon.refresh();    
        }
        protected override Microsoft.Office.Core.IRibbonExtensibility CreateRibbonExtensibilityObject()
        {
          ribbon = new MyRibbon1();
          return ribbon;
        }
    

    I hope this helps.

     

     


    Best Regards, Calvin Gao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Andrew Wiles Thursday, July 28, 2011 7:44 AM
    Thursday, July 28, 2011 7:42 AM
    Moderator

All replies

  • Looks like I can (sort of) do what I want using:

     

    Application.ActiveWorkbook.ShowPivotTableFieldList = false

     

    Unfortunately the effect of this is only temporary so it needs to be called after any action that might open the field list.


    Andrew Wiles - www.it-workplace.com - MDX made simple
    Tuesday, July 26, 2011 3:24 PM
  • <<I am building some functionality that will provide an alternate user interface to add data to the pivot table>>

    Do you create a custom task pane for your own functionality? Do you want to hide the Field list when your custom task pane displayed on UI?

     If so, I think you can use CustomTaskPane.VisibleChanged event to handle the field list to show or not.


    <<Unfortunately the effect of this is only temporary so it needs to be called after any action that might open the field list>> 

    I am unable to find to a method to process this built in pane to disable it. However, as a workaround, I think you can INotifyPropertyChanged Interface to detect if the property has changed. Once changed, you can change the property back to hide the field list again.

    I hope this helps.


    Best Regards, Calvin Gao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, July 27, 2011 11:17 AM
    Moderator
  • Hi Calvin

    I am developing a custom task pane which should be displayed instead of the standard field list.

    In reality what I need is an event that fires when a Pivot table becomes active which I could then use to make my customer task pane visible and hide the standard task pane.

    I am most of the way to what I need by using a combination of Application.SheetPivotTableUpdate and Application.SheetSelectionChange events as well as Application.ActiveWorkbook.ShowPivotTableFieldList. It is a bit messy though as none of these is giving me "exactly" the result I want.

    As a general comment I would say that there are insufficient events raised by the Office object models - you can see this by the number of threads that ask about variations this subject.

     


    Andrew Wiles - www.it-workplace.com - MDX made simple
    Wednesday, July 27, 2011 2:08 PM
  • Hi Andres,

    For your first requirement: suppress the built-in pivottable field list, please follow the steps below:

    1. Create an Excel add-in
    2. Add a Ribbon XML item
    3. Code the XML file like:

    <?xml version="1.0" encoding="UTF-8"?>
    <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="Ribbon_Load">
     <commands>
      <command idMso ="PivotFieldListShowHide"
           enabled ="false"/>  
     </commands> 
    </customUI>
    
    


    In this way, you can disable the "Field List" togglebutton so that suppress the task pane.

    <<In reality what I need is an event that fires when a Pivot table becomes active which I could then use to make my customer task pane visible and hide the standard task pane>>

    For your last description of your intention, I would recommend you to have a look at this document:

    http://msdn.microsoft.com/en-us/library/bb964683.aspx#Y13489

    This document demonstrate how to use custom a togglebutton to hide and unhide your custom task pane.

    I have done a little bit of modifications to satisfy your requirement:

    1. Code the ribbon XML file like below (the custom ribbon in my test solution named "MyRibbon1")

    <?xml version="1.0" encoding="UTF-8"?>
    <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="Ribbon_Load">
     <commands>
      <command idMso ="PivotFieldListShowHide"
           enabled ="false"/>  
     </commands>
     <ribbon>
      <tabs >
       <tab idMso ="TabInsert">
        <group id ="group1" label ="MyGroup">
         <toggleButton id ="togglebutton1"
                 label ="Custom Task Pane"
                 onAction ="togglebutton1_click"
                 getPressed ="togglebutton1_pressed"/>
        </group>
       </tab>
      </tabs>  
     </ribbon>
    </customUI>
    


    2. In MyRibbon1.cs, code the Callbacks region like this:

        #region Ribbon Callbacks
        //Create callback methods here. For more information about adding callback methods, select the Ribbon XML item in Solution Explorer and then press F1
        public void refresh()
        {
          ribbon.InvalidateControl("togglebutton1");
        }
        public void Ribbon_Load(Office.IRibbonUI ribbonUI)
        {
          this.ribbon = ribbonUI;
        } 
        public void togglebutton1_click(Office.IRibbonControl control, bool isPressed)
        {
          Globals.ThisAddIn.pane.Visible = isPressed;
          Globals.ThisAddIn.Application.ActiveWorkbook.ShowPivotTableFieldList = !isPressed;
        }
        public bool togglebutton1_pressed(Office.IRibbonControl control)
        {
          return Globals.ThisAddIn.pane.Visible;
        }
        #endregion
    


    3. In ThisAddIn.cs file:

        private MyRibbon1 ribbon; 
    
        public Microsoft.Office.Tools.CustomTaskPane pane = null;
        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
          Button button1 = new Button (); 
          UserControl uControl = new UserControl ();
          uControl.Controls.Add(button1);
          pane = CustomTaskPanes.Add(uControl, "MyPane");
          pane.Visible = true;
          pane.VisibleChanged += new EventHandler(pane_VisibleChanged);    
        }
    
        void pane_VisibleChanged(object sender, EventArgs e)
        {
          ribbon.refresh();    
        }
        protected override Microsoft.Office.Core.IRibbonExtensibility CreateRibbonExtensibilityObject()
        {
          ribbon = new MyRibbon1();
          return ribbon;
        }
    

    I hope this helps.

     

     


    Best Regards, Calvin Gao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Andrew Wiles Thursday, July 28, 2011 7:44 AM
    Thursday, July 28, 2011 7:42 AM
    Moderator
  • Thank you Calvin, this is very helpful.

    Andrew Wiles - www.it-workplace.com - MDX made simple
    Thursday, July 28, 2011 7:44 AM
  • You are Welcome:-)
    Best Regards, Calvin Gao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, July 29, 2011 3:14 AM
    Moderator