Maximum Iterations

[dyaron]

Hello all...

I have a spreadsheet that I use to track stocks, but it's built to be used as an application. I have the Iterations tab checked, and the Maximum iterations set at 9999. It seems to be fine for about a week or so, as I use it throughout the week, but every few days, I have to reset this because I'm assuming it's down 9999 calculations. Is there a way I can prevent this from happenening, or "reset" the maximum iterations to 9999 everytime before I open the workbook, or close the workbook. Any suggestions on how to accomplish this, and more importantly, when to do this would be great. Thanks guys!

 

[RalphA]

I can't help you on hoiw to maintain the iterations at 9999. But, I can help you with a macro to change that value to 9999, every time you open your workbook. Here's how:

Click on the Macro button, name your macro, say, Iterate9999, select a shortcut key, say Shift+R, and press OK. Now, click on Tools, Options, Ca.lculate, and change the iterations to 9999, then OK, and close your macro.

Now, anytime you poress the shortcut key combination, Ctrl+Shift+R, the macro will un and rewrite the 9999 iteration requirement.

Or, if you prefer to have the macro run everytime you make a selection, then:
Click on the macro button again, choose the macro you have just recorded, then Edit, highlight the code, and do a Copy. Now, go to the sheet tab, and do a right click, and choose View code. In the sub,
"Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
enter Sub yourMacroName()

You can choose to enter the line, Sub yourMacroName() inside any other Sub, so it will run automatically when that Sub runs. I believe there is one that runs on opening a workbook, but I don't recall how to get to it.

Let us know if this solves your problem.
Every time you make any selection, the macro will run.

I believe this will activate your macro everytime you do a change.

 

[RalphA]

My editing time expired, so, I am posting the follow up.

Right Click on the sheet tab, choose View Code, then, on the side, select VBAProject, Microsoft Excel Object, and double click on Tis Workbook. In the code widnow, you should see, Private Sub Workbook_Open(). Just below that line, insert the line,
Sub Iterations999()
Now, everytime you open the workbook, your macro will run, updating the iterations to 999. You can still run the macro manually, whenever you want, by pressing Ctrl+Shift+R.

End Sub