Announcement

Collapse
No announcement yet.

VBA Iterations Code

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA Iterations Code



    Is there a VBA code you can set so that when someone opens an Excel file it will automatically check the iterations in the formula bar to 1000, even if another Excel doc is open that does not have that checked. As well as prevent the warning (circular reference or otherwise) from popping up? The people who will be using this spreadsheet (everyone will have access to this 'Master File' and edit as they need it) will be easily spooked by any errors they receive, so I'd rather just avoid it to start.

    Also this is just for this particular workbook. We don't use a lot of Macro Enabled sheets, so I won't need anything that puts the code on EVERY excel sheet. Really just this one.

    I'm 100% a VBA 'newbie', so any help you can give will need to be very basic instructions. Thank you in advance and I'm keeping my fingers crossed.
    Last edited by oo7miranda; April 3rd, 2014, 01:22. Reason: forgot a portion of the question

  • #2
    Re: VBA Iterations Code

    Any change you make to the formula calculation options will affect all open workbooks. We can try to do a workaround by setting our code to "turn on" when our workbook is active, and "turn off" when deactivated. The other caution is that ti disable the circular error msg, we'll have to disable ALL error messages. This may/may not be a good thing, but I'll include it for now. To install, right-click on your sheet tab, view code. In the top left, double click on the ThisWorkbook module. In the area that opens up, paste this code:
    Code:
    Private Sub Workbook_Activate()
    'Turn off alerts, set iteration, enter manual calculation mode
        With Application
            .DisplayAlerts = False
            .Iteration = True
            .MaxIterations = 1000
            .Calculation = xlCalculationManual
        End With
    End Sub
    
    
    Private Sub Workbook_Deactivate()
    'If our workbook is not active, reset formula settings
    With Application
        .DisplayAlerts = True
        .Iteration = False
        .MaxIterations = 100
        .Calculation = xlAutomatic
    End With
    End Sub
    Close the Visual Basic Editor (VBE), and your workbook should be ready. Again, be mindful of what these changes are doing.
    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

    Comment


    • #3
      Re: VBA Iterations Code

      Thank you for the code. I have a few questions about it after giving it a shot and before I officially release this new version of the workbook.

      1. I didn't realize that it would uncheck the iterations box on all other open excel books. Is there any other way around it? Basically one of the old versions of this template did NOT have that checked. I need to make sure this new version DOES have that checked, whether or not a currently open document does or does not. But if more than one book is open, it needs to be checked on them.

      2. Does this VBA 'deactivate' all of the formulas built into the spreadsheet? Or is that just an odd side affect of something I've mistyped?

      Comment


      • #4
        Re: VBA Iterations Code

        Re: 1 and 2
        Unfortunately, the formula settings affect all spreadsheets and all workbooks. You can't have some on, and some off.
        Best Regards,
        Luke M
        =======
        "A little knowledge is a dangerous thing."

        Comment


        • #5
          Re: VBA Iterations Code

          Is it possible to record a Macro that performs the "File/Options/Enable Iterations/1000" and then write something that runs that macro as soon as the Workbook opens? Or is that a pipe dream?

          Side note, in case I forget to say it, Luke M thank you SOOO much for your help.

          Comment


          • #6


            Re: VBA Iterations Code

            You could. The macro I wrote above is a Workbook_Activate, rather than a Workbook_Open event, and they would both initially work the same way, the way you describe (changes take effect when you open). The former has the advantage in that I set it up so that once the workbook is deactivated (say you want to work on a different workbook) or you close it, the changes go back. Does that make sense?

            You're very welcome.
            Best Regards,
            Luke M
            =======
            "A little knowledge is a dangerous thing."

            Comment

            Working...
            X