Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change to excel settings launching an event
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change to excel settings launching an event
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change to excel settings launching an event
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 "Bob Phillips" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change settings to right-click to delete in Excel? | Setting up and Configuration of Excel | |||
Change Excel Defualt Settings | Excel Discussion (Misc queries) | |||
How do you change the excel number default settings? | Excel Discussion (Misc queries) | |||
how can i change the default settings in excel 2003? | Excel Discussion (Misc queries) | |||
Change Excel Settings | Excel Discussion (Misc queries) |