force automatic calculation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Don - 30 Jan 2008 21:36 GMT

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.

Reply to this Message

JP - 30 Jan 2008 21:54 GMT

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

> Can I force Excel through a registry entry to set Tools | Options |
> Calculations to "Automatic"? I know that I can first open a spreadsheet
[quoted text clipped - 6 lines]
> 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.

Reply to this Message

Don - 30 Jan 2008 22:35 GMT

works great, thanks

> How about this code in the ThisWorkbook module of your personal.xls?
>
[quoted text clipped - 25 lines]
> > 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.

Reply to this Message

Don - 30 Jan 2008 21:57 GMT

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

Reply to this Message

Gord Dibben - 30 Jan 2008 22:42 GMT

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

>Can I force Excel through a registry entry to set Tools | Options |
>Calculations to "Automatic"? I know that I can first open a spreadsheet
[quoted text clipped - 6 lines]
>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.

Reply to this Message

Don - 30 Jan 2008 22:53 GMT

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.

> This problem has been discussed for the past few versions of Excel and has not
> been corrected in 2007.
[quoted text clipped - 15 lines]
> >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.

Reply to this Message