On open set calculation to manual not working all the time

**matt260384** · 07-01-2009, 10:16 PM

Hi,

i'm using the below code to make calculation manual on opening a very complex workbook. This appears to work most of the time, however quite often (maybe connected to having other workbooks open) upon opening it starts calculating. Almost as if it hasn't yet ran the code below and is calculating first. Like i said, this doesn't happen all the time but it is a problem as the workbook requires 3 other workbooks to be open at the same time and is to be used by people who will not know how to cancel the calculation.

Is this a bug and is there a fix for this?

The code below is in the VBAProject folder for the workbook in "ThisWorkbook":

Please Login or Register  to view this content.

Thanks

**shg** · 07-01-2009, 11:19 PM

Welcome to the forum, matt.

Please take a few minutes to read the forum rules, and then edit your post to add code tags.

Thanks.

**matt260384** · 07-01-2009, 11:30 PM

Sorry, done correctly now

**shg** · 07-02-2009, 12:27 AM

Thanks.

I expect that calculation occurs before the Open event is triggered. You'd need to turn off calculation (via the UI or code in another workbook) before opening the other.

**matt260384** · 07-02-2009, 05:34 PM

I see, the problem is novice users will be opening this sheet and calculation is taking about 3 minutes.

One option would be to copy and paste values all of my vlookups but this would take a lot of code to have them rewritten for use again.

Does anyone know of a way to get code to run upon opening excel and before the actual workbook opens?

**shg** · 07-03-2009, 12:47 AM

I retract my earlier comment -- the Open event is triggered before calculation, so it should be working.

Can you post a stripped-down workbook with something that makes it obvious that calculation has occurred on Open (like =NOW(), formatted as mm:ss)