Maximum Iterations


Active Member
Nov 26, 2007
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!


Well-known Member
May 14, 2003
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.


Well-known Member
May 14, 2003
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

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...