Stop whole sheet recalculating after a data input is changed

Hi
First time one here.
I am creating a sheet to calculate returns on a trade system with various rates of commission.
All works ok apart from when the commission value is changed the whole sheet recalculates at the new
commission rate.
I need the already calculated lines to remain as they were.
Any replies gratefully received.
Thanks
Mike

Originally Posted by mikebeas

I am creating a sheet to calculate returns on a trade system with various rates of commission. All works ok apart from when the commission value is changed the whole sheet recalculates at the new commission rate. I need the already calculated lines to remain as they were.

Why don't you want the "whole" worksheet to recalculate? Is there any part of the worksheet that you expect to recalculate automatically?

If not, perhaps the following paradigm is the feature you are looking for.

Right-click on the worksheet tab, click on View Code, and copy the following text and paste into the editing pane of the VBA window.

Please Login or Register  to view this content.

Save the file as "xls" or "xlsm".

To use in Excel, press alt-F8, select the macro that you want to execute, then click on Run.

If you want the worksheet calculation to be disabled when you open the file later, also paste the following macro into the ThisWorkBook object in VBA:

Please Login or Register  to view this content.

Change "Sheet1" to the appropriate worksheet object name (not the worksheet name).

Hi
Many thanks for the reply and advice it is really apreciated.
I have probably not explained my problem very well
I have two input cells trade value E3 say 50
commission rate E4 say 4%
these values are then used to calculate the profit/loss of the trade on say line B7
but the next trade line B8 could be E3 100
E4 2.5%
when I change these values all the sheet re-calculates I only want the particular
line in this case B8 to update and so on.
Thanks in hope again
Mike

Originally Posted by mikebeas

I have probably not explained my problem very well[.] I have two input cells trade value E3 say 50[,] commission rate E4 say 4%[.] these values are then used to calculate the profit/loss of the trade on say line B7[.] but the next trade line B8 could be E3 100 [and] E4 2.5%[.] when I change these values all the sheet re-calculates I only want the particular line in this case B8 to update and so on.

I think I have a better idea about what you are trying to do. But I am still reading a lot in the tea leaves. You would probably get a better answer more quickly if you attached an example Excel file to a response here, and explained the sequence of changes that should trigger recalculation of the latest(?) formula in column B. You might need to click on Go Advanced to see the Attachments icon in the toolbar.

To summarize your example.... Once B7 has been calculated based on E3 and E4, you want to retain that value when B8 is calculated based on E3 and E4, which might change in the interim. Right?

The simplest solution might be: after B7 is calculated, copy it and paste-value back into B7, overwriting the formula with the value.

That could be automated using an event macro. Or it could be semi-automated using a regular macro, either by pressing alt-F8 or associating the macro with a button Form Control.

But there are still some details that are unclear, IMHO.

Alternatively, it might be prudent for you to redesign your worksheet to be more consistent with how Excel works. For example, instead of reusing E3 and E4 for each trade, perhaps you should have the relevant inputs in the same row as the trade. Thus, the formula in B7 might reference E7 and F7; and the formula in B8 might reference E8 and F8. Then, there is no problem with the way that Excel recalculates things.

In any case, I might not be the right person to help you futher. This is a design issue; and I am trying to limit my involvement in people's designs. Hopefully, one of the many other viewers will jump in to help.

Hi
Many thanks again.
I may well go the way of a commission and stake column in each line.
In hope I have also taken your advice and attach a copy of the sheet
Mike