Can't get Application Events to work with .xla

[M. Authement]

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

 

[John.Greenan]

Try a little change:


Option Explicit
global AppClass As clsEventClass

Private Sub Workbook_Open()
Set AppClass = new clsEventClass
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 excel.Application

 

[M. Authement]

John,

Thank you for the suggestion. I received a compile error when I attempted
to make the AppClass declaration global in the ThisWorkbook module
(something about constants, arrays and Declare statements not allowed as
Public members of object modules), so I moved it to a regular module...still
getting the type mismatch error at the same line. I tried eliminating the
Call InitCalcMode in the Workbook_Open sub again, tried changing the
Application.Calculation in the line with the error to
Excel.Application.Calculation and AppClass.App.Calculation, and tried
eliminating the NewWorkbook, SheetActivate, and WorkbookOpen events but
still no luck (though I now do not know where the call to InitCalcMode is
coming from when the add-in opens???). When the line in question is
highlighted in break mode and I hold the mouse over it the tool tip (error
tip?) says Application.Calculation = <type mismatch>. Is it not recognizing
the Application? And I still don't understand why it works as an .xls but
fails as an .xla.

Any more suggesstions? I don't know what else to try.

 

[SimonConroy]

You need to have a workbook open. It's similar to the way Tools
Options is greyed out when there's no workbook open. Add a tem
workbook and you'll be able to see/change Application's Calculatio
property. Then close it.

Cheers,

Simo