Change to excel settings launching an event

[Jeff Standen]

Hi all,

I've got a simple custom command bar that does various useful stuff. One of
the things I'd like it to do is to have a button that controls the
application.calculation parameter. That bit is fine - the .faceid changes
depending on what the setting is so you can easily see what is on.

The problem is how to trap the event of application.calculation being
changed in a macro or in the options dialog, or just checking it at frequent
enough intervals without slowing the app down too much. I've exposed
application events in a class module of the toolbar add-in but something
seems to be wrong as sheetselectionchange and sheetchange don't seem to get
fired when the selection or sheets change. The code for the class module is
below, with the sub that doesn't work.

Thanks for any help

Jeff

Option Explicit
Public WithEvents xlApp As Application


Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)
With CommandBars.FindControl(Tag:="cmdCalcChange")
Select Case Application.Calculation
Case xlCalculationAutomatic
.FaceId = 6735
.TooltipText = "Automatic calculation"
Case xlCalculationManual
.FaceId = 6743
.TooltipText = "Manual calculation"
Case xlCalculationSemiautomatic
.FaceId = 6751
.TooltipText = "Automatic calculation except tables"
End Select
End With
End Sub

 

[Bob Phillips]

Jeff,

Are you initialising application events? Do you have any code such as this
in ThisWorkbook

Dim AppClass As New Class1

Private Sub Workbook_Open()

Set AppClass.xlApp = Application

End Sub


--

HTH

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

 

[Jeff Standen]

It's alright, I sorted it by reopening the add-in. I guess it wasn't
initialised, even though the initialisation code was there already, as I
changed the class code. Dunno

Jeff