Problem with open_workbook sub

[slim]

Thanks to you guys I was able to write a macro that creates a toggle button
allowing me to toggle the calculation mode from automatic to manual. As I
explained in another thread I was having a problem when exiting excel with
the toggle button in manual mode..when I reopened excel the button would be
in manual mode (as it should be), but the calculation mode would be in
automatic.

I was able to fix that by adding a Workbook_Open subroutine to initialize
the button and calc mode. I put this sub under "This Workbook" in
personal.xls. I've included both macros below.

When I open up excel now, without directly opening up a file, no
problems..everything works great. The problem I have now is when I open up
excel by executing an actual excel file. When this happens the initialize
macro screws up because it seems to run before an actual workbook is opened.
The initialization macro will not work without an acual workbook open
because the calculation mode can't be set without a workbook being open.

Hopefully I've explained my problem well enough for you guys to understand.
Does anyone have any suggestions?

Thanks again.


Sub CalcMode()

Dim nState As Long
Dim sMode As String

If Application.Calculation = xlCalculationManual Then
Application.Calculation = xlCalculationAutomatic
nState = msoButtonUp
sMode = "Automatic"

Else: Application.Calculation = xlCalculationManual
Application.CalculateBeforeSave = False
nState = msoButtonDown
sMode = "Manual"

End If

With Application.CommandBars.ActionControl
.State = nState
.TooltipText = "Calculation mode is " & sMode
End With

End Sub


Private Sub Workbook_Open()

With Application.CommandBars("Standard")
With .Controls("Calculation Mode")
If .State = msoButtonUp Then
Application.Calculation = xlCalculationAutomatic
Else
Application.Calculation = xlCalculationManual
End If
End With
End With

End Sub

 

[Jim Rech]

Excel always opens in automatic calc mode. It switches to manual if the
first workbook opened was saved in manual. If you have a Personal.xls it's
probably the first opened. So resave it after making sure you're in
automatic calc mode.

Btw, in my opinion the button state should be set to the calc mode in
opening, not the reverse.

 

[slim]

Jim, thanks for your response.

I switched the Open_Workbook() around to adjust the button to the calc mode.
Save personal.xls with Automatic mode on.

I think the problem that I'm still having is that when I open any workbook,
other than starting a new one(ie. Book1)...then it seems that what is
happening is personal.xls projects load without a physical workbook opening
before the workbook that I'm attempting to open opens up.

Thereby, the Open_Workbook routine is trying to assess the calculation mode
immediatly..but it is unable to perform that operation without a workbook
open..as options are grayed out when no workbooks are open.

The error I get is Runtime error 13 : Type Mismatch

 

[Gord Dibben]

Slim

On the off-chance that you have a couple of typos......

The syntax is Workbook_Open not Open_Workbook

You may not be running anything when Personal.xls starts up.

BTW, Personal.xls is a physical workbook same as any other workbook.

Gord Dibben Excel MVP

 

[Jim Rech]

Right. You can't even check the calc mode state without a visible workbook,
and Personal isn't visible.

What I've done in cases like that is to set a macro to run later using
OnTime. Like this:

Private Sub Workbook_Open()
Application.OnTime DateAdd("s", 1, Now), "SetBtnState"
End Sub

This sets a 1 second delay, which should allow time for the default workbook
to open, before SetBtnState (or whatever you call it) is run.

 

[slim]

That was a typo..it is Workbook_Open ..not the other way around. Thanks.

 

[slim]

Thanks Jim. The 1 second delay seems to have done the job.