ExcelRibbon OnConnection SheetSelectionChangeEvent failure

Oct 17, 2013 at 12:15 PM
I have an odd problem with the SheetSelectionEventHandler in OnConnection from ExcelRibbon.
It just doesn't work with this type of event.

I use Windows 2007 / Office 2007 / Excel-DNA 0.30
    public class MyRibbon : ExcelRibbon {

        public override void OnConnection(object excel, ext_ConnectMode ConnectMode, object AddInInst, ref Array custom) {

            try {
                Microsoft.Office.Interop.Excel.Application appl = (Microsoft.Office.Interop.Excel.Application)excel;
                appl.SheetSelectionChange += new Microsoft.Office.Interop.Excel.AppEvents_SheetSelectionChangeEventHandler(SheetSelectionChangeEvent);
                LogDisplay.WriteLine("SheetSelectionChangeEvent setUp");
            } catch (System.Security.SecurityException excp) {
                LogDisplay.WriteLine("SheetSelectionChangeEvent setUp AddIn Error: " + excp.ToString() + " " + excp.FailedAssemblyInfo.EscapedCodeBase);
            } catch (Exception excp) {
                LogDisplay.WriteLine("SheetSelectionChangeEvent setUp AddIn Error: " + excp.ToString());
            }
        }
        
        public void SheetSelectionChangeEvent(object Sh, Microsoft.Office.Interop.Excel.Range Target) { 
            LogDisplay.WriteLine("SheetSelectionChangeEvent ");
            LogDisplay.WriteLine("SheetSelectionChangeEvent " + Target.get_Address(System.Type.Missing,System.Type.Missing,Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1,System.Type.Missing,System.Type.Missing));
        }

...
Btw, the NewWorkbook event just gets fired once for the initial workbook. If I create subsequently a new workbook, only the WorkbookActivateEvent gets fired.

Did anyone have the same problems?
Coordinator
Oct 19, 2013 at 2:12 PM
Hi,

I've been trying to recreate the problem you report, but everything seems to work as I would expect.
The SheetSelectionChange event fires whenever a different range of cells is selected on a particular sheet, and the NewWorkbook event fires for all new workbooks I add.

The complete .dna file I'm testing with is pasted below.

Please try this, and see if you still see the problem on your system.

Regards,
Govert
<DnaLibrary RuntimeVersion="v4.0" Language="C#">
<Reference Name="Microsoft.Office.Interop.Excel" />
<![CDATA[
using System;
using Excel = Microsoft.Office.Interop.Excel;
using ExcelDna.Integration;
using ExcelDna.Integration.CustomUI;
using ExcelDna.Integration.Extensibility;
using ExcelDna.Logging;

public class MyRibbon : ExcelRibbon 
{
    // public Excel.Application app;
    public override void OnConnection(object excel, ext_ConnectMode ConnectMode, object AddInInst, ref Array custom) 
    {
        Excel.Application app = (Excel.Application)excel;
        try 
        {
            app.SheetSelectionChange += SheetSelectionChangeEvent;
            ((Excel.AppEvents_Event)app).NewWorkbook += NewWorkbookEvent;
            LogDisplay.WriteLine("Events setUp");
        } 
        catch (System.Security.SecurityException excp) 
        {
            LogDisplay.WriteLine("Events setUp AddIn Error: " + excp.ToString() + " " + excp.FailedAssemblyInfo.EscapedCodeBase);
        } 
        catch (Exception excp) 
        {
            LogDisplay.WriteLine("Events setUp AddIn Error: " + excp.ToString());
        }
    }

    public void SheetSelectionChangeEvent(object Sh, Excel.Range Target) 
    { 
        LogDisplay.WriteLine("SheetSelectionChangeEvent " + Target.Address[ReferenceStyle: Excel.XlReferenceStyle.xlA1]);
    }
    
    public void NewWorkbookEvent(Excel.Workbook workbook) 
    { 
        LogDisplay.WriteLine("NewWorkbookEvent " + workbook.Name);
    }
    
}
]]>
</DnaLibrary>
Nov 1, 2013 at 1:49 PM
Many thanks for your reply.

I use NetOffice and my guess is, that NetOffice Event Handling has problems in the OnConnection method. I found out, that if I started the AddIn with Excel it didn't work, but if I started Excel and opened the .xll later it worked. Now I add the EventHandlers later in a thread after running through OnConnection and that might be the solution.