Manual calculation on Open

Dannyh1

Well-known Member
Joined
Nov 18, 2009
Messages
1,144
Hi,

I have the following code

Code:
Private Sub Workbook_Open()
 
Application.Calculation = xlCalculationManual
 
End Sub

The problem is I don't want anything to calculate when the file is opened. This code calculates the spreadsheet & then switches the calc to manual.

Is there a way around this?

Thanks
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Derek Brown

Well-known Member
Joined
Dec 26, 2005
Messages
2,390
I am not quite sure what you are looking for because the code you gave only sets to calculation mode to manual.
You did not say which version of Excel is being used but you can set a workbook to manual calculation using Formulas | Calculation Mode.
However, be warned - opening a workbook with calculation set to manual may affect other workbooks that you open in the same instance of Excel. Also, opening a manual-calculation workbook in the same instance of Excel where a workbook is set to automatic calculation will change the one you are about to open.
Have a look at:
http://support.microsoft.com/kb/214395
 

Dannyh1

Well-known Member
Joined
Nov 18, 2009
Messages
1,144
Hi ,

I'm on Excel 2000 (we're are still in dark ages)

Situation is I have a speadsheet that includes an input sheet.

Once input sheet has been filled in, user clicks 'name sheets' button which will name all relevant sheets to correct name (from input sheet)

I have a summary sheet, that calculates each of these sheets.

The problem:

When the input sheet is being filled in, the summary sheet tries to calculate against sheet names that do not yet exist (because name sheet macro has not yet been run) - This is causing the calculation to be very slow.

I would turn back on the autocalculation at the end of my 'name sheets' macro.

The way the macro works in 1st post is it calculates spreadsheet to 100% & then turns calculation to manual.

I'm looking for a way to Open a workbook & for the manual calculation be turned on immediately.

Any suggestions?

Thanks again
 

Dannyh1

Well-known Member
Joined
Nov 18, 2009
Messages
1,144
Hi,

Calling MVP's....

can any of you guys help on this? :eek:

Thanks
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,364

ADVERTISEMENT

not an MVP :)
quite impossible i think without a trick - a workaround:
you cannot do it in this way. the way i see to do it is set Calculation to manual before open it (it applies to the Application in general. so my idea is - create a "Dummy" file - open it instead of the other one and put in it the code to do the following:
- set calculation to manual
- open the other workbook
the dummy file may be hidden to not stay in the way.
- when the other workbook is opened you may close the dummy file and when done set back Calculation to Automatic.

hope it will work for you.
 

Derek Brown

Well-known Member
Joined
Dec 26, 2005
Messages
2,390
Not an MVP but I agree with bobsan42 - the link that I gave you explains that you cannot be totally in control of calculation mode when opening a workbook unless it is the only workbook opened in that instance of Excel.
Opening from another 'application', whether it be another workbook, a VB.net program etc. is probably the most reliable way.
However, I am a bit puzzled - if autocalculation is acceptable before saving/closing the workbook, then it must also be acceptable on opening (?). So perhaps you could hide (or, in VBA, 'very hide') the input worksheet until the user clicks a button to set calculation to manual and unhide the worksheet?
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

You can disable calculations in a particular worksheet so if it's the summary sheet given you issues you could disable it in this sheet and then perhaps fire the sheet calculation by user intervention (eg a button on the sheet).
 

Dannyh1

Well-known Member
Joined
Nov 18, 2009
Messages
1,144
Thanks guys

Richard - could you please tell me how disable calcs in a specific sheet?

Thanks again.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,067
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Code:
Worksheets("Sheet name").Enablecalculation = False
 

Dannyh1

Well-known Member
Joined
Nov 18, 2009
Messages
1,144
Thanks Rory,

Still couldn't get this to resolve my issue though.

The way I got round it (in case anyone is interested :LOL:) -was to add in all the formulas via code after sheets had been named.

Cheers.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,485
Messages
5,596,442
Members
414,065
Latest member
kamlkham

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top