C# Excel worksheet_change not ALWAYS firing

Viewed 158

I've been working on an excel add-in.

This is the constructor for the addin:

        public AddinModule()
        {
            Application.EnableVisualStyles();
            InitializeComponent();
            AddinFinalize += new ADXEvents_EventHandler(FinalizeAddIn);
            AddinInitialize += new ADXEvents_EventHandler(InitializeAddIn);
            OnError += new ADXError_EventHandler(OnErrorAddIn);
            EventDel_CellsChange += new Excel.DocEvents_ChangeEventHandler(CellsChange);
        }

There is a button that, among other things, creates a new worksheet, and after it has been created, the CellsChange method is added to the worksheet.Change event, as follows:

//some code
                CreateWorkSheet();
                sheet = ExcelApp.ActiveSheet as Excel.Worksheet;
                sheet.Change += EventDel_CellsChange;
//some code

Column A values are loaded when the worksheet is created. This works as expected. Columns B to E values depend on the one selected in the previous column. The problem that I'm having is that a change in any of these columns not always trigger the event. I have obviously debugged the addin, adding breakpoints to the cellschange method, but the result is the same: sometimes it breaks in the breakpoint, and sometimes it doesn't. When it does stop, everything works just fine. Once the event is not triggered, it won't work again, unless the worksheet is deleted, and added by pressing the previously mentioned button. Am I missing something?

Finally, the cellsChange method:

private void CellsChange(Excel.Range Target)
        {

            Excel.Worksheet sheet = null;
            Excel.Range rng = null;

            Excel.Range columns = null;

            Excel.Range column = null;
            try
            {
                foreach (Excel.Range c in Target.Cells)
                {
                    if (c.Row != 1 && c.Column < (int)Columnas.Column6 && c.Value2 != null)
                    {

                        sheet = CurrentInstance.ExcelApp.ActiveSheet as Excel.Worksheet;
                        string[] values = null;
                        string AValue;
                        string BValue;
                        switch (c.Column)
                        {
                            case (int)Columnas.A:
                                values = GetBValues(c.Value2.ToString());
                                rng = sheet.Cells[c.Row, (int)Columnas.B] as Excel.Range;
                                break;
                            case (int)Columnas.B:
                                AValue = (string)(sheet.Cells[c.Row, (int)Columnas.A] as Excel.Range).Value;
                                values = GetCValues(AValue, c.Value2.ToString());
                                rng = sheet.Cells[c.Row, (int)Columnas.C] as Excel.Range;
                                break;
                            case (int)Columnas.C:
                                AValue = (string)(sheet.Cells[c.Row, (int)Columnas.A] as Excel.Range).Value;
                                BValue = (string)(sheet.Cells[c.Row, (int)Columnas.B] as Excel.Range).Value;
                                values = GetDValues(AValue, BValue, c.Value2.ToString());
                                rng = sheet.Cells[c.Row, (int)Columnas.D] as Excel.Range;
                                break;
                            case (int)Columnas.D:
                                if (c.Value2.ToString() == FUTURE)
                                {
                                    AValue = (string)(sheet.Cells[c.Row, (int)Columnas.A] as Excel.Range).Value;
                                    BValue = (string)(sheet.Cells[c.Row, (int)Columnas.B] as Excel.Range).Value;
                                    string moneda = (string)(sheet.Cells[c.Row, (int)Columnas.C] as Excel.Range).Value;
                                    values = GetEValues(AValue, BValue, moneda, c.Value2.ToString());
                                    rng = sheet.Cells[c.Row, (int)Columnas.E] as Excel.Range;
                                }
                                break;
                            default:
                                break;
                        }


                        if (values != null)
                        {

                            sheet.Unprotect(Type.Missing);
                            columns = rng.Columns;
                            column = columns[1] as Excel.Range;
                            column.Validation.Delete();
                            SetRangeFormat(column, FormatType.Text);
                            column.Validation.Add(Excel.XlDVType.xlValidateList, Excel.XlDVAlertStyle.xlValidAlertInformation,
                                    Type.Missing, string.Join(Separator, values), Type.Missing);

                            column.Validation.InCellDropdown = true;
                            column.Locked = false;

                            sheet.Protect(Type.Missing, false, true, false, false, true, true, true, false, false, true, false, false, true, true, true);
                        }
                    }
                }
            }
            catch (Exception e)
            {
                Debug.WriteLine(e.Message);
            }
            finally
            {
                if (sheet != null)
                {
                    Marshal.ReleaseComObject(sheet);
                }

                if (columns != null) Marshal.ReleaseComObject(columns);
                if (rng != null) Marshal.ReleaseComObject(rng);
                if (column != null)
                {

                    Marshal.ReleaseComObject(column);
                }
            }
        }

0 Answers
Related