This project has moved. For the latest updates, please go here.

Fail to remove Worksheet.ChangeEvent handler

Jul 26, 2012 at 8:46 AM
Edited Jul 26, 2012 at 8:50 AM

Hi,

I'm trying to attach and detach Worksheet.ChangeEvent according to button click actions. The "+=" works fine, but the "-=" does not work. 

Below are the three test methods. After the OnDetachEvent() is invoked, the ValueChangeHandler still works.

 

Excel.Application app = new Excel.Application(null, ExcelDnaUtil.Application);

public void OnAttachEvent() {

 Debug.WriteLine("into attach");

app.ActiveSheet.ChangeEvent += ValueChangeHandler;

}

public void OnDetachEvent() {

Debug.WriteLine("into detach");

app.ActiveSheet.ChangeEvent -= ValueChangeHandler;

}

public void ValueChangeHandler(Excel.Range Target) {

Debug.WriteLine("in change handler");

}

Is the "-=" operator the right approach to remove the event handler?

Coordinator
Jul 26, 2012 at 9:02 AM

do you use NetOffice 1.4 or 1.5 ?

Jul 26, 2012 at 9:10 AM

I use 1.5.0.0

Coordinator
Jul 26, 2012 at 9:25 AM

ActiveSheet is object/COMObject in NetOffice. this code can't be true.
app.ActiveSheet.ChangeEvent += ValueChangeHandler;

but anyway. store the worksheet reference in a variable. for example:

 workSheet.ChangeEvent += new Excel.Worksheet_ChangeEventHandler(workSheet_ChangeEvent);
 workSheet.ChangeEvent -= new Excel.Worksheet_ChangeEventHandler(workSheet_ChangeEvent);

this works fine becaues if you call app.ActiveSheet 2 times you have 2 different proxies(for the same object in COM Server(Excel) memory of course).

Sebastian

Jul 26, 2012 at 9:42 AM

Thank you. It works.

According to your answer, I get the different proxy for the active sheet in OnAttachEvent() and OnDetachEvent()

Sorry that I simplify "(app.ActiveSheet as Worksheet).ChangeEvent += ValueChangeHandler" as "app.ActiveSheet.ChangeEvent += ValueChangeHandler" when editing the post.