none
Excel - dynamically added event get lost when opening new workbook RRS feed

  • Question

  • Hallo,

    I do have a problem with dynamically added selectionChange Events. It works when I open Excel and the addin starts up. But as soon as I open new/another workbook is opend (book2) the event does not fire in any sheet anymore (book1 neither book2).

    When I add one or more sheets within the same workbook (book2) the event is triggered. But as soon as I switch to the other workbook (book1) and repeat that action, the event fires only in that workbook (book1). 

    Does anyone have an explantion why that is happening?

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Xml.Linq;
    using InteropExcel = Microsoft.Office.Interop.Excel;
    using Office = Microsoft.Office.Core;
    using ToolsExcel = Microsoft.Office.Tools.Excel;
    
    namespace Pro_Wunderkiste4Excel
    {
        public partial class AddIn_Wunderkiste4Excel
        {
            #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
    
            InteropExcel.DocEvents_SelectionChangeEventHandler SelectionChangeEvent;
            InteropExcel.DocEvents_ChangeEventHandler ChangeEvent;
            InteropExcel.Worksheet ws; 
            
            private void ThisAddIn_Startup(object sender, System.EventArgs e)
            {
                System.Diagnostics.Debug.Print(this.Application.Workbooks.Count.ToString() + "Start of Wunderkiste");
                //this.Application.NewWorkbook += new Microsoft.Office.Interop.Excel.AppEvents_NewWorkbookEventHandler(Excel_NewWorkbook_EventHandler);
                ((InteropExcel.AppEvents_Event)Application).NewWorkbook += new InteropExcel.AppEvents_NewWorkbookEventHandler(Excel_NewWorkbook_EventHandler);
                this.Application.WorkbookNewSheet += new InteropExcel.AppEvents_WorkbookNewSheetEventHandler(ExcelWB_NewWorksheet_EventHandler);
                this.Application.WorkbookOpen += new InteropExcel.AppEvents_WorkbookOpenEventHandler(Excel_WorkbookOpen_EventHandler);
    
                SelectionChangeEvent = new InteropExcel.DocEvents_SelectionChangeEventHandler(ExcelWS_SelectionChange_EventHandler);
                ChangeEvent = new InteropExcel.DocEvents_ChangeEventHandler(ExcelWS_Change_EventHandler);
    
            }
    
            private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
            {
                System.Diagnostics.Debug.Print(this.Application.Workbooks.Count.ToString() + "Stop of Wunderkiste");
                ((InteropExcel.AppEvents_Event)Application).NewWorkbook -= new InteropExcel.AppEvents_NewWorkbookEventHandler(Excel_NewWorkbook_EventHandler);
                this.Application.WorkbookNewSheet -= new InteropExcel.AppEvents_WorkbookNewSheetEventHandler(ExcelWB_NewWorksheet_EventHandler);
                this.Application.WorkbookOpen -= new InteropExcel.AppEvents_WorkbookOpenEventHandler(Excel_WorkbookOpen_EventHandler);
            }
    
    
            #region ExcelEvents NewOrOpen_Events
    
            private void Excel_NewWorkbook_EventHandler(InteropExcel.Workbook wbCurrentWB)
            {
                System.Diagnostics.Debug.Print("---------------");
                System.Diagnostics.Debug.Print(wbCurrentWB.Name + " - workbook created");
                System.Diagnostics.Debug.Print(wbCurrentWB.Sheets.Count.ToString() + " - count of sheets ");
                ExcelWB_AddEvent_SelectionChangedInWS(wbCurrentWB);
            }
    
            private void ExcelWB_NewWorksheet_EventHandler(InteropExcel.Workbook wbCurrentWB, object Sh)
            {
                ExcelWS_AddEvent_SelectionChangedInWS(wbCurrentWB.ActiveSheet);
            }
    
            private void Excel_WorkbookOpen_EventHandler(InteropExcel.Workbook wbCurrentWB)
            {
                System.Diagnostics.Debug.Print("---------------");
                System.Diagnostics.Debug.Print(wbCurrentWB.Name + " - workbook opened");
                System.Diagnostics.Debug.Print(wbCurrentWB.Sheets.Count.ToString() + " - count of sheets ");
                ExcelWB_AddEvent_SelectionChangedInWS(wbCurrentWB);
            }
            #endregion
            
            #region ExcelEvents SelectionChange_Event_AddOrRemove
            private void ExcelWB_AddEvent_SelectionChangedInWS(InteropExcel.Workbook wbCurrentWB)
            {
    
                if (wbCurrentWB.Sheets.Count > 0)
                {
                    for (int i = 1; i <= wbCurrentWB.Sheets.Count; i++)
                    {
                        //((InteropExcel.Worksheet)this.Application.ActiveWorkbook.Sheets[i]).Select();
                        //ws = wbCurrentWB.ActiveSheet;
                        //ws = (InteropExcel.Worksheet)this.Application.ActiveWorkbook.Sheets[i];
                        //ws = wbCurrentWB.Sheets[i];
                        ExcelWS_AddEvent_SelectionChangedInWS(wbCurrentWB.Sheets[i]);
                    }
    
                }
            }
    
            private void ExcelWS_AddEvent_SelectionChangedInWS(InteropExcel.Worksheet wbCurrentWS)
            {
                string ParentName;
                ParentName = wbCurrentWS.Parent.Name;
                System.Diagnostics.Debug.Print(ParentName + "." + wbCurrentWS.Name + " - change event added");
    
                wbCurrentWS.SelectionChange += SelectionChangeEvent;
                wbCurrentWS.Change += ChangeEvent;
                
            }
            #endregion
    
            internal void ExcelWS_SelectionChange_EventHandler(InteropExcel.Range RaTarget)
            {
                System.Diagnostics.Debug.Print(RaTarget.Address + " - selected a");
    
            }
    
            internal void ExcelWS_Change_EventHandler(InteropExcel.Range RaTarget)
            {
                System.Diagnostics.Debug.Print(RaTarget.Address + " - selected b");
    
            }
    
    
        }
    }

    Monday, June 6, 2016 3:58 PM

Answers

All replies

  • Hi StefanM83,

    on your above mentioned code I made some tests.

    I have reproduce the issue at our side and I find that when 1st time addin loaded the events are fired.

    after that when we change the selection at that time event not called automatically.

    but when we add new sheet at that time events are called but only within that workbook and not working in other workbook.

    if I find something I will let you know.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, June 7, 2016 12:37 AM
    Moderator
  • Hi Deepak, 

    have you found anything?

    thx

    Stefan

    Tuesday, June 14, 2016 6:58 AM
  •  Hi StefanM83,

    I have found the similar issue like you in thread the following code solves the issue.

    you can look into this code. it is for word but you can try to understand the way how can it used in your code.

    private void ThisAddIn_Startup(object sender, System.EventArgs e)
            {
                ((Word.ApplicationEvents4_Event)Application).NewDocument += ThisAddIn_NewDocument;
                Application.DocumentOpen += ThisAddIn_DocumentOpen;
                Application.DocumentBeforeClose += Application_DocumentBeforeClose;
            }
    
            private void Application_DocumentBeforeClose(Word.Document Doc, ref bool Cancel)
            {
                (Globals.Factory.GetVstoObject(Application.ActiveDocument)).BeforeDoubleClick -= HostDoc_BeforeDoubleClick;
            }
    
            private void ThisAddIn_DocumentOpen(Word.Document Doc)
            {
                (Globals.Factory.GetVstoObject(Application.ActiveDocument)).BeforeDoubleClick += HostDoc_BeforeDoubleClick;
            }
    
            private void ThisAddIn_NewDocument(Word.Document Doc)
            {
                (Globals.Factory.GetVstoObject(Application.ActiveDocument)).BeforeDoubleClick += HostDoc_BeforeDoubleClick;
            }
    
            private void HostDoc_BeforeDoubleClick(object sender, ClickEventArgs e)
            {
                System.Windows.Forms.MessageBox.Show((sender as Document)?.Name + " " + "On double click !", "Alert!");
            }
    
            private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
            {
            }

    let us know if it worked for you or not

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, June 14, 2016 7:07 AM
    Moderator
  • thx that solved it.

    It seems or at least as far as I understand that you have to generate a host item or host control for each object what shell be handled by code written in VS.
    But why it works partially without doing so, I can't explain.  

    I'm not really an expert of VSTO may you can shed light on that curiosity? 

    I changed the code as follows:

    old

            private void ExcelWS_AddEvent_SelectionChangedInWS(InteropExcel.Worksheet wbCurrentWS)
            {
                string ParentName;
                ParentName = wbCurrentWS.Parent.Name;
                System.Diagnostics.Debug.Print(ParentName + "." + wbCurrentWS.Name + " - change event added");
    
                wbCurrentWS.SelectionChange += SelectionChangeEvent;
                wbCurrentWS.Change += ChangeEvent;
                
            }

    new

    private void ExcelWS_AddEvent_SelectionChangedInWS(InteropExcel.Worksheet wbCurrentWS) //add event for selected/active sheet in active workbook
            {
                Microsoft.Office.Interop.Excel.Worksheet nativeWorksheet = wbCurrentWS; //generate a host item for an Excel worksheet
                if (nativeWorksheet != null)
                {
                    string ParentName;
                    ParentName = wbCurrentWS.Parent.Name;
                    Microsoft.Office.Tools.Excel.Worksheet vstoSheet = Globals.Factory.GetVstoObject(nativeWorksheet);
                    vstoSheet.SelectionChange += ExcelWS_SelectionChangeEvent;
                    System.Diagnostics.Debug.Print(ParentName + "." + wbCurrentWS.Name + " - Selection change event added");
                }
            }

    That new code is based on the following sources:

    1. MSDN: Extending Word Documents and Excel Workbooks in VSTO Add-ins at Run Time
    2. MSDN: Getting Extended Objects from Native Office Objects in Document-Level Customizations
    3. MSDN: Programmatic Limitations of Host Items and Host Controls
    4. MSDN: Workbook Host Item
    5. stackoverflow: control method of worksheet is not available

    Wednesday, June 15, 2016 4:22 PM
  • Hi StefanM83,

    its good to hear from you that you got a solution for your issue.

    thanks for sharing a code and links which are helpful to others who are facing same issue like you.

    from your post they can also get their solution.

    I think that Some document, workbook, and worksheet events in the native Word and Excel object models are raised only at the application level. For example, the E:Microsoft.Office.Interop.Word.ApplicationEvents4_Event.DocumentBeforeSave event is raised when a document is opened in Word, but this event is defined in the Microsoft.Office.Interop.Word.Application class, rather than the Microsoft.Office.Interop.Word.Document class.

    When you use only native Office objects in your VSTO Add-in, you must handle these application-level events and then write additional code to determine whether the document that raised the event is one that you have customized. Host items provide these events at the document level, so that it is easier to handle the events for a specific document. You can generate a host item and then handle the event for that host item.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, June 16, 2016 6:56 AM
    Moderator
  • Hi Deepak Panchal10,

    sorry for my late answer but I was quite busy recently and haven't got to finalize this discussion.

    I'm trying to follow your thoughts but it isn't entirely clear for me. 

    the events NewWorkbookWorkbookNewSheet and WorkbookOpen are defined in Microsoft.Office.Interop.Excel.AppEvents...
    but SelectionChangeEventHandler and ChangeEventHandler in Microsoft.Office.Interop.Excel.DocEvents...

    As far as I understand anything defined in ...AppEvents... will be always caught by the AddIn unlike events defined in ...DocEvents... 
    I do not understand the connection to native object on application level. In my opinion anything is working fine on application level the subscription to events gets rather lost on document level, why ever.

    According to the description for host objects on msdn.microsoft, it could be that the way it was initially subscribed to those events (on doc level) are only valid for the active workbook (application). But if you want to have document specific code/events you have to get its VstoObject what extents it's functionally in order to handle object specific code. That object has to be used to subscribe to document level events etc.

    My explanation above is properly not that clear, it isn't that clear for me either. May you can express it in proper way and correct it where necessary

     


    Tuesday, June 28, 2016 2:25 PM