Disable Calculation

[hanksteph]

I have a multiple page spreadsheet worksheet that operates off of random number functions and once I get a result on the first worksheet, I need it permanently saved on that sheet and the results moved to the next worksheet and so on.....I have disabled automatic calculation and set it to manual and then gone into the visual basic editor and totally disabled calculation on the worksheet I need to save. That works fine to a point (While I am working on it, that particular session), but once I close the spreadsheet, it seems to lose its memory that I had disabled calculation on the first worksheet, for when I reopen the spreadsheet, calculation is enabled on all worksheets once again.....Is there a way to fix this issue or do I simply have to keep going into the VB editor and disabling calculation every time I open the spreadsheet. I tried protecting the sheet and that did not help.....Any ideas?

Thanks in advance.....

[Richard Buttrey]

Hi,

The control of the calculation mode is at the Excel Application level and not at an individual workbook level. Hence if you change calculation to manual in the VB editor without changing the default setting for the application (Excel Options from the 2007 Office button, or Tools Options calculation in 2003), the next time you open the application the default automatic parameter will be in play again.

If you want to ensure a particular workbook always has manual calc set, put the instruction Application.Calculation = xlCalculationManual in the workbook Open event, and to be on the safe side put Application.Calculation = xlCalculationAutomatic in the Workbook Close event.

HTH

[hanksteph]

What I am actually wanting to accomplish is to make sure that certain worksheets within the workbook are completely disabled from calculating at all, even when I do manually calculate the other worksheets......This is actually a game design and each worksheet represents a turn in the game.....I can't have the previous turns always recalculating themselves.....I'm thinking something along lines of "Application Calculation = xlSheet1DisableCalculation" Based on what you are saying.....Would there be something I could do along those lines? I suppose I could run a macro to do this? Might be the easiest? Any opinion there?

Thanks again....

[hanksteph]

I tried making a Macro to do this and it did not work.....Now I'm definatly a bit confused....

[Richard Buttrey]

Hi,

You could set the whole application to manual calculation as explained before, then when you want to calculate a particular sheet use either Shift F9 when the sheet you wish to calculate is the active sheet, or use a macro. i.e.

Please Login or Register  to view this content.

You could put this in a procedure and run it at any time, or in the" MySheetName" Activate event so that it automatically calculates every time you activate that sheet.

HTH

[DonkeyOte]

You can use

Please Login or Register  to view this content.

whether you would want to or not....

perhaps have a read through of Charles Williams' site: http://www.decisionmodels.com/calcsecretse.htm

[hanksteph]

Richard,

I have been setting the whole application to manuel calculation from the beginning. The issue is that when I manually calculate, there are certain worksheets that I do not wish to calculate at all. I suspect Donkeyote is on to something with the code he has posted.

It was also pointed out to me that I could also simply copy the sheet and paste special values only thereby eliminating the formulas completely while retaining the results......This would work as long as I maintained a master copy of the game with the formlulas in all the worksheets....However, If I can do it using code, I believe it will be better in the long run.

[Richard Buttrey]

Hi,

With the whole workbook set to manual calc. (i.e. at application level), all you need to do with a macro is use the mysheetname.calculate instruction to calculate only the sheets you want to refresh. All other sheets will still remain uncalculated.

HTH

[hanksteph]

Gentlamen,

Thank you, this issue is solved. Here is the code placed in "ThisWorkbook"

Please Login or Register  to view this content.

Works just perfect.....Thanks again.....

[jonesse2]

I had this problem and it turned out to be a circular reference error, which might work for someone else