Calculation mode

[K Dales]

I have created a custom "Calculation" toolbar that gives
me the functions "Calc Sheet", "Calc Full", and "Calc
Now." I also added a button that uses VBA code to toggle
between Manual, Automatic, and Semiautomatic calc modes.
The code also changes the caption on the button to
read "MANUAL", "AUTO" or "SEMI" depending on the currently
selected mode. So far so good...

The problem: When I start up Excel, the caption on my
button does not necessarily reflect the current
calculation mode. It always comes up as "AUTO." As soon
as I press the button it reads the mode and the caption
changes appropriately, but I sure would like to know by
that button what mode I am in when I start up, without
having to press the dang thing.

I have tried various routines to run on workbook open,
activate, etc... I try reading the current calculation
mode and setting the button caption accordingly. The
routines work fine when I test them with Excel already up
and running, so I am fairly confident the code works, but
it still displays improperly when I first start Excel.

Perhaps it is timing - that I am trying to read the calc
mode when it is still in its default setting, before
applying any saved settings? I still can't figure it
out. Anyone out there who can help?

Here's the code to toggle the mode and set the caption - I
have it in my Personal.xls workbook:

Sub CalcMode()

Dim CurrentMode As Integer

CurrentMode = Application.Calculation

Select Case CurrentMode
Case xlCalculationAutomatic
Application.Calculation = xlCalculationManual
Case xlCalculationManual
Application.Calculation = xlCalculationSemiautomatic
Case xlCalculationSemiautomatic
Application.Calculation = xlCalculationAutomatic
Case Else
Application.Calculation = xlCalculationAutomatic
End Select

CurrentMode = Application.Calculation

Select Case CurrentMode
Case xlCalculationAutomatic
Application.CommandBars("Calculation").Controls
(1).Caption = "AUTO"
Case xlCalculationManual
Application.CommandBars("Calculation").Controls
(1).Caption = "MANUAL"
Case xlCalculationSemiautomatic
Application.CommandBars("Calculation").Controls
(1).Caption = "SEMI"
Case Else
Application.CommandBars("Calculation").Controls
(1).Caption = "???"
End Select

End Sub

Thanks in advance...
K Dales

 

[Bob Phillips]

Do you have code to create the toolbar as you open the workbook, or do you
just have a manually built toolbar?

If the former, you need to run the test to set the caption accordingly, if
the latter then it's probably because the toolbar saves an initial state.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[K Dales]

Thanks Bob. I am using a manually created toolbar that is
saved as part of my Personal.xls workbook. I have tried
getting the code to run when the workbook opens to read
the mode and set the caption accordingly but it the code
always seems to capture the default state (AUTO) instead
of the saved state. Maybe this is because the saved state
is not read by Excel until after the Workbook_Open and
Workbook_Activate events? I don't know, that seems to be
where I run into trouble. I just don't know whcih event I
can tie my code to in order to read the saved state
properly and set that caption.

 

[Bob Phillips]

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

I think your problem stems from the fact that Calculate is an application
property, not workbook, and it takes the state of the first workbook open
ed. So if you are opening this particular workbook after this, it's state
will assume that of the first workbook opened.

If you want to override this, you will need to save it with the workbook,
and read it when you open the workbook and set accordingly.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)