row-based formula entry

Posted by: richardg-fp on 8 September 2017, 1:58 pm EST

  • Posted 8 September 2017, 1:58 pm EST

    I’m new to this and am looking for suggestions to do the following:


    1                    The user enters a formula in A1, let’s say “=SUM(2:4)” using the formula text box which requires a row range (forced by the appropriate selection mode). This is easy, BUT:


    2                    As the user completes the entry, the following actions take place automatically:


    3                    Formulas are put into columns, say, B:D in row 1 as follows
    B1    “=SUM(B2:B4)”
    C1...”=SUM(C2:C4)”
    D1   “=SUM(D2:D4)”


    4                    B1:D1 are locked


    5                    A1 displays, as text, “=SUM(2:4)”


    I imagine that a custom cell type with the use of EditModeOff may be the answer.


    Could someone point me in the right direction?

  • Replied 8 September 2017, 1:58 pm EST

    Hello,

    Using the latest version of Spread for Windows Forms I am not able to replicate the behavior explained by you.It shows the sum correctly and it doesn't put any formula in other cells.I used the following code to place the Formula Bar on the Form,

            Dim formulabox As New FarPoint.Win.Spread.FormulaTextBox
            formulabox.Location = New Point(50, 8)
            formulabox.Size = New Size(80, 20)
            Controls.Add(formulabox)
            formulabox.Attach(FpSpread1)
            FpSpread1.Sheets(0).Cells(1, 0, 3, 3).Text = 1

    When I put =SUM(2:4) for cell A1 in formula text box the result comes up as "12" in cell A1, and rest everything works absolutely fine.You may test this behavior with the latest build of Spread for windows forms and let me know your observations.

     

     

    Thanks,

     


     

     

     

     

     

  • Replied 8 September 2017, 1:58 pm EST

    Hello Richard, 

    Here is how to achieve that you are looking for:

    1. Set the ReferenceStyle property of the ActiveSheet to R1C1. 

    2. Use the formulaTextBox1_Leave event to copy the formula from A1 cell to other cells. 

    3. In the same event set the Locked property of the other cells to true.

    In form load event set: fpSpread1.ActiveSheet.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1;

    Next,

            private void formulaTextBox1_Leave(object sender, EventArgs e)

            {

                for (int i = 1; i <= 3; i++)

                {

                    fpSpread1.ActiveSheet.Cells[0, i].Formula = fpSpread1.ActiveSheet.Cells[0, 0].Formula;

                    fpSpread1.ActiveSheet.Cells[0, i].Locked = true;

                }

            }

    I hope it helps.

    Regards, 

  • Replied 8 September 2017, 1:58 pm EST

    Hi DeepakSharma


    My question is how do I achieve the behaviour described!


    Thanks


     

  • Replied 8 September 2017, 1:58 pm EST

    Hello,

    I apologize for misunderstanding your query, there is no direct method to achieve this we may create a custom Formula and then apply the formula to the desired cell.Here is Thread that has the information about creating a custom formula.

     

     

    Thanks,

  • Replied 8 September 2017, 1:58 pm EST

    Hi Deepak, thanks for responding.


    I have read that thread but I'm not sure I understand how it helps.


    Are you suggesting that:


    a)  In Step 1 of my task, the user enters a custom formula, say, "=ROWSUM(2:4)";
    b)  Steps 3 & 4 of my task are done by statements in ROWSUM Evaluate;
    c)  Step 5 of my task is accomplished by getting ROWSUM to return "=ROWSUM(2:4)" as a text string?


    Doesn't this go wrong if the user then inserts a new row in between 2 and 4 as all the formulas would be updated as follows:


    i)    the custom formula in A1 will become ROWSUM(2:5) which would be correct;
    ii)   the re-evulation of A1 will cause new formulas "=SUM(B2:B5)", "=SUM(C2:C5)","=SUM(D2:D5)" to be inserted into B1, C1 & D1 respctively which would be correct.
    iii)  BUT, depending on how the sheets formulas get updated when a row is inserted, the formulas in B1, C1 & D1 might then, in turn, be modified to respond to the change in range and become "=SUM(B2:B6)", "=SUM(C2:C6)","=SUM(D2:D6)" which would be wrong and would not correspond to the formula in A1.


    So, I guess, I am asking whether the use of a custom formula will really do what I want.


    The only other way I can think of is:
    1   Define a custom Cell Type which simply displays its own formula as text, in this case, "=ROWSUM(2:4)";
    2   When the user completes the A1 formula entry (how do I know this? EditModeOff ?), statements are executed which place the correct formulas into B1, C1 & D1.
    3   If a new row were inserted between 2 & 4, the formulas in A1, B1, C1 & D1 would all be correctly updated and so would the displayed results in those cells.


    But, I don't really know enough to know whether I am talking rubbish or simply do not understand enough!


    Am I wrong to worry about the effect of inserting a new row on the formulas?


    If so, is my alternative possible?


    If neither will work, is there any other way?


    Thanks 


    Richard

  • Replied 8 September 2017, 1:58 pm EST

    Richard, you may place your code in a method and then call that method from the formulaTextBox1_Leave as well as fpSpread1_EditChange events. 

    Below is the code snippet:

            private void fpSpread1_EditChange(object sender, FarPoint.Win.Spread.EditorNotifyEventArgs e)

            {

                if(fpSpread1.ActiveSheet.ActiveCell.Text.StartsWith("="))

                ApplyFormula();

            }

            private void formulaTextBox1_Leave(object sender, EventArgs e)

            {

                ApplyFormula();

            }

            private void ApplyFormula()

            {

                for (int i = 1; i <= 3; i++)

                {


                    fpSpread1.ActiveSheet.Cells[0, i].Formula = fpSpread1.ActiveSheet.Cells[0, 0].Formula;


                    fpSpread1.ActiveSheet.Cells[0, i].Locked = true;


                }

            }

    Regards, 

  • Replied 8 September 2017, 1:58 pm EST

    Hi Harish


    Thanks for your help.


    I'll give this a try and let you know how I get on!


    regards

  • Replied 8 September 2017, 1:58 pm EST

    Hi Harish,


    I hit a problem:


    The formula textbox is only entered if the User selects a range using the mouse or clicks in the formula textbox.


    If the User simply types in a formula, the formulaTextBox1_Leave event is never fired.


    Is there an event that is always fired after a formula is entered or edited - maybe when the formula editor is left?


    Thanks

  • Replied 8 September 2017, 1:58 pm EST

    The formulaTextBox1_Leave events does not fire if you try to Tab out of the FormulaTextbox. It will fire on clicking Enter key or when you click outside the FormulaTextbox. Since the FormulaTextBox is liked to the Cell, the change in formula is commit to the DataModel of the spread only when the Cell leaves the edit mode. Pressing Enter key moves the cell out of edit mode and fires the leave event of the FormulaTextbox. 

    If you want your code to be executed on pressing the TabKey, you may use the KeyUp event of the FormulaTextBox. You may also use the fpSpread1_LeaveCell event. 

    Regards, 

  • Replied 8 September 2017, 1:58 pm EST

    Hi Harish


    It does not work, I'm afraid.


    When the user starts entering a formula directly into the cell, ApplyFormula() is executed when he enters the first =. I know this because I have set a different background colour for locked cells.


    However, no formula is copied. (I believe this is because the formula is not applied to the cell until the editor is exited - I found that, if the user starts entering a second formula, the first formula is copied!)


    regards

  • Replied 8 September 2017, 1:58 pm EST

    Hi Harish


    I need the code to be executed whenever the formula in a cell is edited or changed by the user.


    The problem is this:


    The Spread formulaTextBox editor does not start until the user clicks in the formulaTextBox or selects a range with the mouse.


    If the user enters a formula directly into a cell without selecting a range with the mouse, the cell's own formula editor is used and not the formulaTextBox editor. In this case, the TextBox_Leave event is not fired when the user completes the formula editing because the formulaTextBox editor was never started.


    I need the code to be executed even if the user enters the formula directly into the cell.


    thanks

  • Replied 8 September 2017, 1:58 pm EST

    Scott


    Thanks

  • Replied 8 September 2017, 1:58 pm EST

    Hello,


    You can move your code to the UserFormulaEntered event.

Need extra support?

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

Learn More

Forum Channels