Class Module for setting Automatic Calc
 

I use Excel 2000 at work and as you are aware there is a bug with "view
all windows in taskbar" option for shared workbooks. It gets turned off
when you open a shared workbook. I have fixed the bug with the help of
this newsgroup, by inserting a class module, which resets it to "on"

My question is, the same problem occurs with calculation option, it
gets set to manual. Can I reset it to automatic with a class module, or
by some other means, and if so, how? TIA

Greg

Class Module for setting Automatic Calc
 

I've never noticed a bug with the calculation setting.

Excel determines the calculation from the first workbook that's opened in that
session.

I think I'd try this first....

Option Explicit
Sub auto_open()

Dim wkbk As Workbook

Set wkbk = Workbooks.Add
Application.Calculation = xlCalculationAutomatic
'wkbk.close savechanges:=false

End Sub

But if you close that temporary workbook, I think (I don't recall for sure) that
excel will pick up the calculation mode from the next workbook opened--if there
isn't an activeworkbook.

If you feel like experimenting, uncomment that .close line and open a workbook
that was saved with calculation set to manual.

If xl changes calculation mode back to manual, then don't use that "wkbk.close"
line.


=========
But if you really think it's some other thing changing the calculation mode (can
you post what you do and when it changes that setting), you could use an
application event like this:

Inside the ThisWorkbook module:

Option Explicit
Public WithEvents xlApp As Application
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set xlApp = Nothing
End Sub
Private Sub Workbook_Open()
Set xlApp = Application
End Sub
Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
If Application.Calculation < xlCalculationAutomatic Then
Application.Calculation = xlCalculationAutomatic
Beep 'just so you're aware that it changed.
End If
End Sub

It actually checks the calculation setting each time you select a different
range.

GregR wrote:

I use Excel 2000 at work and as you are aware there is a bug with "view
all windows in taskbar" option for shared workbooks. It gets turned off
when you open a shared workbook. I have fixed the bug with the help of
this newsgroup, by inserting a class module, which resets it to "on"

My question is, the same problem occurs with calculation option, it
gets set to manual. Can I reset it to automatic with a class module, or
by some other means, and if so, how? TIA

Greg

--

Dave Peterson

Class Module for setting Automatic Calc
 

Dave, I think what happens is, if the workbook is opened by more than
one individual at the same time, the calculation gets set to manual.
That is only a problem for me when I run a macro that copies a sheet
from the workbook and pastes the values into a new workbook. If
calculations have not occured, the new workbook has errors.

I've taken your suggestion and added a line to my code which forces
calculations just before the copy function. I am assuming that the
line:

Application.Calculation = xlCalculationAutomatic

will force a calculations.

Greg

Class Module for setting Automatic Calc
 

Application.CalculateFull

forces calculation.

--
Regards,
Tom Ogilvy


"GregR" wrote in message
oups.com...
Dave, I think what happens is, if the workbook is opened by more than
one individual at the same time, the calculation gets set to manual.
That is only a problem for me when I run a macro that copies a sheet
from the workbook and pastes the values into a new workbook. If
calculations have not occured, the new workbook has errors.

I've taken your suggestion and added a line to my code which forces
calculations just before the copy function. I am assuming that the
line:

Application.Calculation = xlCalculationAutomatic

will force a calculations.

Greg

Class Module for setting Automatic Calc
 

Thanks Tom

Greg

Class Module for setting Automatic Calc
 

I've never seen anyone post this kind of problem (and I never used shared
workbooks in real life).

But maybe someone else is opening the workbook, toggling the calculation to
manual, saving the workbook and the other person opens that workbook as the
first workbook resulting in manual calculation setting.

(But I think you're much better off adding the protection for your macro into
the code of your macro!)

GregR wrote:

Dave, I think what happens is, if the workbook is opened by more than
one individual at the same time, the calculation gets set to manual.
That is only a problem for me when I run a macro that copies a sheet
from the workbook and pastes the values into a new workbook. If
calculations have not occured, the new workbook has errors.

I've taken your suggestion and added a line to my code which forces
calculations just before the copy function. I am assuming that the
line:

Application.Calculation = xlCalculationAutomatic

will force a calculations.

Greg

--

Dave Peterson

Class Module for setting Automatic Calc
 

I agree. I, also am not intimateley familiar with shared workbooks.
I'll keep an eye and see if I can figure it out. Thanks for your input.

Greg