## row-based formula entry

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

• Post Options:

Posted 8 September 2017, 1:58 pm EST

<FONT size=3 face="CG Omega">I’m new to this and am looking for suggestions to do the following:</FONT>

<SPAN style="mso-fareast-font-family:'CG Omega';mso-bidi-font-family:'CG Omega';"><SPAN style="mso-list:Ignore;"><FONT size=3 face="CG Omega">1</FONT><SPAN style="FONT:7pt 'Times New Roman';">                    </SPAN></SPAN></SPAN><FONT size=3 face="CG Omega">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:</FONT>

<SPAN style="mso-fareast-font-family:'CG Omega';mso-bidi-font-family:'CG Omega';"><SPAN style="mso-list:Ignore;"><FONT size=3 face="CG Omega">2</FONT><SPAN style="FONT:7pt 'Times New Roman';">                    </SPAN></SPAN></SPAN><FONT size=3 face="CG Omega">As the user completes the entry, the following actions take place automatically:</FONT>

<SPAN style="mso-fareast-font-family:'CG Omega';mso-bidi-font-family:'CG Omega';"><SPAN style="mso-list:Ignore;"><FONT size=3 face="CG Omega">3</FONT><SPAN style="FONT:7pt 'Times New Roman';">                    </SPAN></SPAN></SPAN><FONT size=3 face="CG Omega">Formulas are put into columns, say, B:D in row 1 as follows
B1<SPAN style="mso-spacerun:yes;">    </SPAN>“=SUM(B2:B4)”
C1...”=SUM(C2:C4)”
D1<SPAN style="mso-spacerun:yes;">   </SPAN>“=SUM(D2:D4)”</FONT>

<SPAN style="mso-fareast-font-family:'CG Omega';mso-bidi-font-family:'CG Omega';"><SPAN style="mso-list:Ignore;"><FONT size=3 face="CG Omega">4</FONT><SPAN style="FONT:7pt 'Times New Roman';">                    </SPAN></SPAN></SPAN><FONT size=3 face="CG Omega">B1:D1 are locked</FONT>

<SPAN style="mso-fareast-font-family:'CG Omega';mso-bidi-font-family:'CG Omega';"><SPAN style="mso-list:Ignore;"><FONT size=3 face="CG Omega">5</FONT><SPAN style="FONT:7pt 'Times New Roman';">                    </SPAN></SPAN></SPAN><FONT size=3 face="CG Omega">A1 displays, as text, “=SUM(2:4)”</FONT>

<FONT face="CG Omega"><FONT size=3>I imagine that a custom cell type with the use of </FONT><SPAN style="FONT-SIZE:10pt;">EditModeOff</SPAN><FONT size=3> may be the answer.</FONT></FONT>

<FONT size=3 face="CG Omega">Could someone point me in the right direction?</FONT>

• Post Options:

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,

formulabox.Location = New Point(50, 8)
formulabox.Size = New Size(80, 20)
FpSpread1.Sheets(0).Cells(1, 0, 3, 3).Text = 1

When I put <font size="3" face="CG Omega">=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.</font>

Thanks,

• Post Options:

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.

Next,

private void formulaTextBox1_Leave(object sender, EventArgs e)

{

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

{

}

}

I hope it helps.

Regards,

• Post Options:

Replied 8 September 2017, 1:58 pm EST

Hi DeepakSharma

My question is how do I achieve the behaviour described!

Thanks

• Post Options:

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,

• Post Options:

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

• Post Options:

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:

{

ApplyFormula();

}

private void formulaTextBox1_Leave(object sender, EventArgs e)

{

ApplyFormula();

}

private void ApplyFormula()

{

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

{

}

}

Regards,

• Post Options:

Replied 8 September 2017, 1:58 pm EST

Hi Harish

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

regards

• Post Options:

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

• Post Options:

Replied 8 September 2017, 1:58 pm EST

<span class="Apple-style-span" style="font-family:Tahoma;">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. </span>

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,

• Post Options:

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

• Post Options:

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

• Post Options:

Replied 8 September 2017, 1:58 pm EST

Scott

Thanks

• Post Options:

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

• #### ComponentOne

Forums for all current editions of the ComponentOne .NET UI control product line, including ComponentOne Studio and ComponentOne Studio for Xamarin.

• #### ActiveReports

Forums for all versions of ActiveReports Developer and Server