I am trying to create a shortcut method for toggling between Auto/Manual
calculation mode. I have two menu items, one in the right-click menu and
one off of the worksheet menu. As part of both menu items, I update the
..State property based on Auto or Manual. In order to make this work if the
user chooses the Tool/Options method of changing calculation mode I am
attempting to use a class module/WithEvents with various application events.
The code below works fine when I run it from the .xls file, however, as soon
as I save/install it as an .xla file I get a 'Run-time error 13: Type
mismatch' error at the commented line in the InitCalcMode procedure.

I have tried eliminating the Call InitCalcMode in the Open procedure
thinking the workbook open event would capture it, moved the Set AppClass
line in the Open procedure to the last line, and tried changing the If
Application.Calculation to If App.Calculation and If AppClass.App with no
luck. I am guessing that there is something different about programming for
..xla mode vs .xls but I don't know what it is. Any help is greatly
appreciated and sorry for the long post but I wanted to give as much
information as I could. BTW, I am using XL2003 and WinXP.

In the ThisWorkbook module:

Option Explicit
Dim AppClass As New clsEventClass

Private Sub Workbook_Open()
Set AppClass.App = Application
Call MakeMenu
Call InitCalcMode
Application.OnKey "+^{D}", "modStartEndTime.StartEndTime"
End Sub


In a class module named clsEventClass:

Option Explicit
Public WithEvents App As Application

Private Sub App_NewWorkbook(ByVal Wb As Excel.Workbook)
Call InitCalcMode
End Sub

Private Sub App_SheetActivate(ByVal Sh As Object)
Call InitCalcMode
End Sub

Private Sub App_SheetCalculate(ByVal Sh As Object)
Call InitCalcMode
End Sub

Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)
Call InitCalcMode
End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Excel.Workbook)
Call InitCalcMode
End Sub


In a general code module:

Sub InitCalcMode()
Dim oCtrl1 As CommandBarButton, oCtrl2 As CommandBarButton

On Error Resume Next
Set oCtrl1 = CommandBars(1).FindControl(Tag:="PIMS
TIPS").Controls("AutoCalc")
Set oCtrl2 = CommandBars("Cell").FindControl(Tag:="PIMS
TIPS").Controls("AutoCalc")
On Error GoTo 0

If Application.Calculation = xlCalculationAutomatic Then ****Error
occurs here****
oCtrl1.State = msoButtonDown
oCtrl1.ShortcutText = "AutoCalc ON"
oCtrl2.State = msoButtonDown
oCtrl2.ShortcutText = "AutoCalc ON"
Else
oCtrl1.State = msoButtonUp
oCtrl1.ShortcutText = "AutoCalc OFF"
oCtrl2.State = msoButtonUp
oCtrl2.ShortcutText = "AutoCalc OFF"
End If
End Sub