locked
Unable to unsubscribe from an event RRS feed

  • Question

  • Compiler:  VS2013
    Excel:  (Office 2013)

    Problem:  Let's say I decide to update text in a large group of selected cells (e.g.  A1:A26000)   The SheetChange event will fire just as many times.  What I'd like to do is temporarily disable the event handler, then re-enable it once all the changes are made  (I'm thinking the AfterCalculate event handler would be the ideal spot to re-enable the "SheetChange" event)

    Below is a stripped down version of the Add-In

    private void ThisAddIn_Startup(object sender, System.EventArgs e) {

    // I subscribe to the SheetChange Event this.Application.SheetChange += Application_SheetChange; ... } /********

    SheetChange Event Handler

    **********/ private void Application_SheetChange(object o, Excel.Range T) { try { if( /*Some condition*/ ) { //Do something } else { /************************************ * The following line generates the following error. * "Cannot assign to 'Application_SheetChange' because it is a 'method group' */ this.Application_SheetChange -= Application_SheetChange; /************************************** * Similar 'method group' errors happen hear as well */ if (this.Application_AfterCalculate == null) { this.Application_AfterCalculate += Application_AfterCalculate; } } } catch (Exception ex) { // Do Excption stuff } finally { // Release Allocated Resources }


    Friday, March 3, 2017 7:58 PM

Answers

  • The reason for registering the SheetChange event is that there are times when additional processing may be required if a particular sheet is active;

    However, I did find a work around for my issue.  I set up a boolean flag.

    If we happen to be on that special sheet, then we will follow a different branch of logic.

    If we're not on that special sheet, then I won't bother testing for that sheet again until AfterCalculate has fired (I reset the flag to true)

    Thanks for your input though.  I learned a few things I didn't know.

    • Marked as answer by GermanEZI Monday, March 6, 2017 7:22 PM
    Monday, March 6, 2017 7:22 PM

All replies

  • Not sure how you would code it, but you want to use whatever corresponds to this (used in VBA) where, in this case, Application is the Excel Application and not your application.

    Application.EnableEvents = False

    When I run code that changes a sheet in anyway, I like to use (again, in VBA)

        With Application
            .ScreenUpdating = False
            xlCalc = .Calculation  'Store the setting to allow reset 
            .Calculation = xlCalculationManual
            .EnableEvents = False
            .DisplayAlerts = False
        End With
        
        'Code here to do stuff
        
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
            .DisplayAlerts = True
            .Calculation = xlCalc
        End With


    Friday, March 3, 2017 10:47 PM
  • Hello,

    >>What I'd like to do is temporarily disable the event handler, then re-enable it once all the changes are made 

    In that situation, I think there is no need to registry SheetChange event. Or registry the event handler after all the changes are made.

    According to AppEvents_Event.AfterCalculate event: This event occurs after all Calculate, AfterRefresh, and SheetChange events.  It is the last event to occur after all refresh processing and all calc processing have completed, and it occurs afterCalculationState is set to xlDone

    So I think it is improper to re-enable the SheetChange event in AfterCalculatue event.

    Besides, you may try to use ApplicationClass.EnableEvents property as Bernie suggested.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 6, 2017 8:04 AM
  • The reason for registering the SheetChange event is that there are times when additional processing may be required if a particular sheet is active;

    However, I did find a work around for my issue.  I set up a boolean flag.

    If we happen to be on that special sheet, then we will follow a different branch of logic.

    If we're not on that special sheet, then I won't bother testing for that sheet again until AfterCalculate has fired (I reset the flag to true)

    Thanks for your input though.  I learned a few things I didn't know.

    • Marked as answer by GermanEZI Monday, March 6, 2017 7:22 PM
    Monday, March 6, 2017 7:22 PM
  • Thank you Bernie.   While your solution doesn't quite fit my circumstances, it is something I didn't know prior to your post.  I'll keep it in mind.


    Monday, March 6, 2017 7:24 PM