Event to fire when formula based cells are changed

Posted by: sushmanalam31 on 26 October 2017, 6:05 pm EST

    • Post Options:
    • Link

    Posted 26 October 2017, 6:05 pm EST

    I need to perform some functionality when the value in a formula cell changes. I am using FarPoint.Win.Spread.FpSpread.change event but it is not firing when formula cell value changes.

    I know As the value of the cell is a formula value, when the formula is recalculated the actual cell value is not changed because the formula remains the same, only the result of its evaluation is changed. Then how to handle this change event.

    I tried DefaultSheetDataModel object’s Changed Event but it is not working.

    Kindly provide your suggestions on it.

  • Posted 27 October 2017, 7:46 pm EST

    Hello,

    Sheet DataModel’s Change event will fire for any change that affects the data in the cell, which can be value, formula, note, or tag.

    I tested the behavior with an application .

    In the sample application if you change the Value of Cell A1 or B1 it fires the two times for cell’s value change and other is for Formula cell change. Please refer to the sample application attached and let me know in case it does not work you.

    Thanks,

    Deepak Sharma

    SpreadWinFormulaChange.zip

  • Posted 5 November 2017, 8:20 pm EST

    Hi Deepak,

    Thanks for the response.

    I tried the sample application provided by you. It is working fine if we have formula for only 1 cell.

    Tested scenario - I applied formula for 3 cells C1, D1, E1 which depends on A1 and B1. I am changing the color of all the 3 formula based cells when changes for A & B occurs. In this case the color of only c1 is getting changed i.e., the event is not firing for D1 and E1.

    I need an event to be fired when any of the formula based cell is updated.

    Below is the sample code I tried . please provide your suggestions.

    private void Form1_Load(object sender, EventArgs e)

    {

    fpSpread1.ActiveSheet.Cells[0, 2].Formula = “SUM(A1,B1)”;

    fpSpread1.ActiveSheet.Cells[0, 3].Formula = “SUM(A1,B1)”;

    fpSpread1.ActiveSheet.Cells[0, 4].Formula = “SUM(A1,B1)”;

    defModel =(FarPoint.Win.Spread.Model.DefaultSheetDataModel) fpSpread1.ActiveSheet.Models.Data;

    defModel.Changed += DefModel_Changed;

    }

        private void DefModel_Changed(object sender, FarPoint.Win.Spread.Model.SheetDataModelEventArgs e)
        {
    
            //MessageBox.Show("data model change");
            fpSpread1.ActiveSheet.Cells[e.Row, e.Column].ForeColor = Color.Red;
        }
    

    Thanks,

    Sushma.

  • Posted 8 November 2017, 5:17 pm EST

    Hi,

    I am waiting for the response. Please provide some solution for the above post.

    Thanks,

    Sushma

  • Posted 8 November 2017, 9:29 pm EST

    Hi Sushma,

    The event is fired correctly for all effected cells. You can use the code as follows:

    
      private void Form1_Load(object sender, EventArgs e)
            {
                fpSpread1.ActiveSheet.Cells[0, 2].Formula = "SUM(A1,B1)";
                fpSpread1.ActiveSheet.Cells[0, 3].Formula = "SUM(A1,B1)";
                fpSpread1.ActiveSheet.Cells[0, 4].Formula = "SUM(A1,B1)";
    
                fpSpread1.ActiveSheet.Cells[0, 5].Formula = "SUM(A1,B1)";
                fpSpread1.ActiveSheet.Cells[0, 6].Formula = "SUM(A1,B1)";
                defModel =(FarPoint.Win.Spread.Model.DefaultSheetDataModel) fpSpread1.ActiveSheet.Models.Data; 
                defModel.Changed += DefModel_Changed;
            }
    
            private void DefModel_Changed(object sender, FarPoint.Win.Spread.Model.SheetDataModelEventArgs e)
            {
                if (e.ColumnCount > 1)
                {
                    for (int i = e.Column; i <= e.ColumnCount+1; i++)
                    {
                        fpSpread1.ActiveSheet.Cells[e.Row, i].ForeColor = Color.Red;
                    }
                }
                
            }
    
    

    Thanks,

    Deepak Sharma

  • Posted 10 November 2017, 12:26 am EST

    Hi Deepak,

    Thanks for the response…

    I have gone through the solution you provided. It is working fine for the last post of mine.But I am facing a different issue. The value for columncount is column number of the last formula based cell. suppose if formulas for C1 and G1 is A1+B1 then the column count is 5 and it changes the color for all the cells between C1 and G1 eventhough their value is not updating(as they dont have any formula).

    private void Form1_Load(object sender, EventArgs e)

    {

    fpSpread1.ActiveSheet.Cells[0, 2].Formula = “SUM(A1,B1)”; //C1

    fpSpread1.ActiveSheet.Cells[0, 3].Value = 100; //D1

    fpSpread1.ActiveSheet.Cells[0, 4].Value = 200; //E1

    fpSpread1.ActiveSheet.Cells[0, 5].Value = 300; //F1

    fpSpread1.ActiveSheet.Cells[0, 6].Formula = “SUM(A1,B1)”; //G1

    defModel =(FarPoint.Win.Spread.Model.DefaultSheetDataModel) fpSpread1.ActiveSheet.Models.Data;

    defModel.Changed += DefModel_Changed;

    }

    In the above scenario only C1 and G1 have to change the color not the rest. One more instance for better understanding is suppose H1 is manually entered value and if K1 has A1+H1 formula. If I change H1 then only K1 needs to change its color.

    Hope I am not confusing you. Sorry for not clearing this scenario in my previous post.

    Thanks in Advance,

    Sushma

  • Posted 14 November 2017, 3:46 am EST

    Hi Sushma,

    In that case you can handle the event using the code as below:

    
     private void DefModel_Changed(object sender, FarPoint.Win.Spread.Model.SheetDataModelEventArgs e)
            {    
                    fpSpread1.ActiveSheet.Cells[e.Row, e.Column].ForeColor = Color.Red;
                    fpSpread1.ActiveSheet.Cells[e.Row, e.ColumnCount+1].ForeColor = Color.Red;
            }
    
    

    Thanks,

    Deepak Sharma

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels