none
Events for reordering sheets and changing sheet tab color RRS feed

  • Question

  • In our Excel 2010 Ribbon add-in, we have added a "sheet navigation" custom task pane that helps users navigate workbooks that might contain hundreds of sheets.  The task pane includes tabs with 'views' listing sheets in various ways, including the actual sheet order, alphabetic order, sheet tab color order, and so on.  All is working as designed, except for two things:

    1) When a user drags and drops a sheet tab to a new position along the bottom of the main Excel window, we need to be aware of that event.

    2) When a user sets or changes the sheet tab color (by right-clicking the tab and selecting 'Tab Color'), we need to be aware of that event.

    I've tried using command override in my ribbon.xml file (using idMso="TabOrder" and idMso="SheetTabColorGallery"), but my associated onAction methods are not firing.

    Any ideas how I might solve this?

    Thanks!

    Monday, October 17, 2011 8:39 PM

All replies

  • You might show us the xml code and then maybe we can help spot something.
    Kind Regards, Rich ... http://greatcirclelearning.com
    Tuesday, October 18, 2011 1:31 AM
  • Hello,

    > I've tried using command override in my ribbon.xml file (using idMso="TabOrder" and idMso="SheetTabColorGallery"), but my associated onAction methods are not firing.

    "TabOrder" just doesn't apply; its tooltip states: "Change the tab order of controls on the page". And it is not possible to intercept using an item from a Ribbon gallery.

    You need to check both things (Worksheet.Index and Worksheet.Tab.Color) in the CommandBars.OnUpdate event. Note that the event is called really many times and running a lot of code in it may slow Excel down. I'd consider starting a timer (when the conditions are met) and running all the code bunch in the Timer event. Be aware: if you use the Excel object model in that code, the event must occur in the main thread and you need to use a proper Timer component such as System.Windows.Forms.Timer.


    Regards from Belarus (GMT + 2),

    Andrei Smolin
    Add-in Express Team Leader
    Tuesday, October 18, 2011 7:05 AM
  • Hi All,

    I also tried to override the built-in gallery control by using following XML and callback:

      <command idMso ="SheetTabColorGallery" onAction ="tabColor_change"/>
    

            public void tabColor_change(Office.IRibbonControl control, ref bool cancel)
            {
                MessageBox.Show("Color changed");
                cancel = false;
            }
    

    But it seems that built-in gallery control doesn't work like some other controls, such as button controls. 

    @Anonymous, I will continue to do some researches on this question and consult some senior engineers. Before I can provide some suggestions, I guess that you would have to add a "Refresh" button into you custom task pane to refresh your view list manually every time you make change in sheet order or color.

    Good day,


    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.

    Tuesday, October 18, 2011 8:19 AM
    Moderator
  • Thanks, Andrei and Calvin.   Based on your answers, for now we'll provide a manual Refresh button, and await any further insight from MS senior engineers.

    Best,

    Anonymous

    Tuesday, October 18, 2011 1:09 PM