none
Excel CommandBars OnUpdate event handler stops responding RRS feed

  • Question

  • VS2010+ Office 2010.

    This is an application add-in that utilizes the Commandbars OnUpdate event to monitor the user selection, especially on certain shapes.

    However, i have noticed that the event handler stop responding frequently. For example, if a new workbook is opened, the event handler won't be triggered for any selection made in the new workbook.

    Or this event seems to interfere with SheetSelectionChange event, after the SheetSelectionChange event fired the OnUpdate event is not being fired anymore.

    Normally i could re-hook the event handler to make it work again. However it will hard to anticipate all the scenarios and re-hooking event everywhere may not be good to performance.

    Anyone noticed similar behavior before?

    I will start a new addin solution to see if I can post some code here.


    • Edited by ForeignKey Tuesday, November 27, 2012 4:59 PM
    Tuesday, November 27, 2012 4:58 PM

Answers

  • Hi ForeignKey,

    Thanks for posting in the MSDN Forum.

    I'm suppose your didn't find a correct way to subscribe the OnUpdate event. I provide following snippet for you to do further trouble shooting. I hope it can help you.

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Xml.Linq;
    using Microsoft.Office.Tools.Excel;
    using Microsoft.VisualStudio.Tools.Applications.Runtime;
    using Excel = Microsoft.Office.Interop.Excel;
    using Office = Microsoft.Office.Core;
    namespace ExcelWorkbook1
    {
        public partial class ThisWorkbook
        {
            private Office.CommandBars bars = null;
            private void ThisWorkbook_Startup(object sender, System.EventArgs e)
            {
                bars = Application.CommandBars;
               bars.OnUpdate += new Office._CommandBarsEvents_OnUpdateEventHandler(CommandBars_OnUpdate);
            }
            void CommandBars_OnUpdate()
            {
                MessageBox.Show("Fire!");
            }
            private void ThisWorkbook_Shutdown(object sender, System.EventArgs e)
            {
            }
            #region VSTO Designer 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(ThisWorkbook_Startup);
                this.Shutdown += new System.EventHandler(ThisWorkbook_Shutdown);
            }
            #endregion
        }
    }

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, November 28, 2012 6:28 AM
    Moderator

All replies

  • Hi ForeignKey,

    Thanks for posting in the MSDN Forum.

    I'm suppose your didn't find a correct way to subscribe the OnUpdate event. I provide following snippet for you to do further trouble shooting. I hope it can help you.

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Xml.Linq;
    using Microsoft.Office.Tools.Excel;
    using Microsoft.VisualStudio.Tools.Applications.Runtime;
    using Excel = Microsoft.Office.Interop.Excel;
    using Office = Microsoft.Office.Core;
    namespace ExcelWorkbook1
    {
        public partial class ThisWorkbook
        {
            private Office.CommandBars bars = null;
            private void ThisWorkbook_Startup(object sender, System.EventArgs e)
            {
                bars = Application.CommandBars;
               bars.OnUpdate += new Office._CommandBarsEvents_OnUpdateEventHandler(CommandBars_OnUpdate);
            }
            void CommandBars_OnUpdate()
            {
                MessageBox.Show("Fire!");
            }
            private void ThisWorkbook_Shutdown(object sender, System.EventArgs e)
            {
            }
            #region VSTO Designer 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(ThisWorkbook_Startup);
                this.Shutdown += new System.EventHandler(ThisWorkbook_Shutdown);
            }
            #endregion
        }
    }

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, November 28, 2012 6:28 AM
    Moderator
  • Hi, Tom:

    thanks for the reply. I did figure it out by comparing my code with your snippet.

    I believe what I have done wrong is hooking the event to CommandBar object directly, like:

    Application.CommandBar.OnUpdate += new Office._CommandBarsEvents_OnUpdateEventHandler

    And this will lead to all those problems I described above.

    After i switched to a local variable, everything works fine. Maybe I missed sth. obvious but this is what I found.

    Wednesday, March 6, 2013 6:51 PM
  • Hallo ForeignkKey,

    I've just came accross the same problem. Have you figured out why you cannot hook some event to an object directly? 

    Thursday, June 9, 2016 7:13 AM
  • I have a similar problem may the thread Excel - dynamically added event get lost when opening new workbook gives you some more ideas
    Tuesday, June 28, 2016 2:29 PM