force automatic calculation

[Don]

Can I force Excel through a registry entry to set Tools | Options |
Calculations to "Automatic"? I know that I can first open a spreadsheet
which is already set to Automatic and that all subsequently opened
spreadsheets will then be Automatic, but that is a pain. I really want to
force Excel into Automatic mode every time I open any spreadsheet whether the
spreadsheet is set to Automatic or Manual. I also know I can make a startup
xls and set it to Automatic. The problem with this is that if I shut down
excel during the day and then reopen later by double-clicking on a file which
happens to be Manual, then I'm set to Manual.....so I think the only way is
to force Excel through a registry entry....thoughts??? Thanks.

 

[JP]

How about this code in the ThisWorkbook module of your personal.xls?

Private WithEvents XLApp As Excel.Application

Private Sub Workbook_Open()
Set XLApp = Excel.Application
End Sub

Private Sub XLApp_NewWorkbook(ByVal Wb As Workbook)
XLApp.Calculation = xlCalculationAutomatic
End Sub

Private Sub XLApp_WorkbookOpen(ByVal Wb As Workbook)
XLApp.Calculation = xlCalculationAutomatic
End Sub


HTH,
JP

 

[Don]

Can I force Excel through a registry entry to set Tools | Options |

Calculations to "Automatic"? I know that I can first open a spreadsheet
which is already set to Automatic and that all subsequently opened
spreadsheets will then be Automatic, but that is a pain. I really want to
force Excel into Automatic mode every time I open any spreadsheet whether the
spreadsheet is set to Automatic or Manual. I also know I can make a startup
xls and set it to Automatic. The problem with this is that if I shut down
excel during the day and then reopen later by double-clicking on a file which
happens to be Manual, then I'm set to Manual.....so I think the only way is
to force Excel through a registry entry....thoughts??? Thanks.

I also would like a registry entry that turns Interations on and sets Max
Its to 999.

 

[Don]

works great, thanks

How about this code in the ThisWorkbook module of your personal.xls?

Private WithEvents XLApp As Excel.Application

Private Sub Workbook_Open()
Set XLApp = Excel.Application
End Sub

Private Sub XLApp_NewWorkbook(ByVal Wb As Workbook)
XLApp.Calculation = xlCalculationAutomatic
End Sub

Private Sub XLApp_WorkbookOpen(ByVal Wb As Workbook)
XLApp.Calculation = xlCalculationAutomatic
End Sub


HTH,
JP

 

[Gord Dibben]

This problem has been discussed for the past few versions of Excel and has not
been corrected in 2007.

There is no Registry entry ever come up in these discussions that I can recall.

Same for Iterations settings in your later post.


Gord Dibben MS Excel MVP

 

[Don]

I did get some VB from 'JP' to put in my personal.xls, that works great to
take care of the Automatic Calculation issue and is fine by me as a great
substitute for a registry entry. Hopefully someone will post some VB as a
workaround for the iterations.

 

[nde]

I have the same issue, but what are your refering to when you state :

"your personal.xls?"

Is this a specific file or generic file, like book1 when i create a new xls ?

Thanks,

Nde

 

[Gord Dibben]

See Excel help on personal macro workbook>create a macro


Gord Dibben MS Excel MVP