Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation mode
I have created a custom "Calculation" toolbar that gives
me the functions "Calc Sheet", "Calc Full", and "Calc Now." I also added a button that uses VBA code to toggle between Manual, Automatic, and Semiautomatic calc modes. The code also changes the caption on the button to read "MANUAL", "AUTO" or "SEMI" depending on the currently selected mode. So far so good... The problem: When I start up Excel, the caption on my button does not necessarily reflect the current calculation mode. It always comes up as "AUTO." As soon as I press the button it reads the mode and the caption changes appropriately, but I sure would like to know by that button what mode I am in when I start up, without having to press the dang thing. I have tried various routines to run on workbook open, activate, etc... I try reading the current calculation mode and setting the button caption accordingly. The routines work fine when I test them with Excel already up and running, so I am fairly confident the code works, but it still displays improperly when I first start Excel. Perhaps it is timing - that I am trying to read the calc mode when it is still in its default setting, before applying any saved settings? I still can't figure it out. Anyone out there who can help? Here's the code to toggle the mode and set the caption - I have it in my Personal.xls workbook: Sub CalcMode() Dim CurrentMode As Integer CurrentMode = Application.Calculation Select Case CurrentMode Case xlCalculationAutomatic Application.Calculation = xlCalculationManual Case xlCalculationManual Application.Calculation = xlCalculationSemiautomatic Case xlCalculationSemiautomatic Application.Calculation = xlCalculationAutomatic Case Else Application.Calculation = xlCalculationAutomatic End Select CurrentMode = Application.Calculation Select Case CurrentMode Case xlCalculationAutomatic Application.CommandBars("Calculation").Controls (1).Caption = "AUTO" Case xlCalculationManual Application.CommandBars("Calculation").Controls (1).Caption = "MANUAL" Case xlCalculationSemiautomatic Application.CommandBars("Calculation").Controls (1).Caption = "SEMI" Case Else Application.CommandBars("Calculation").Controls (1).Caption = "???" End Select End Sub Thanks in advance... K Dales |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation mode
Do you have code to create the toolbar as you open the workbook, or do you
just have a manually built toolbar? If the former, you need to run the test to set the caption accordingly, if the latter then it's probably because the toolbar saves an initial state. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "K Dales" wrote in message ... I have created a custom "Calculation" toolbar that gives me the functions "Calc Sheet", "Calc Full", and "Calc Now." I also added a button that uses VBA code to toggle between Manual, Automatic, and Semiautomatic calc modes. The code also changes the caption on the button to read "MANUAL", "AUTO" or "SEMI" depending on the currently selected mode. So far so good... The problem: When I start up Excel, the caption on my button does not necessarily reflect the current calculation mode. It always comes up as "AUTO." As soon as I press the button it reads the mode and the caption changes appropriately, but I sure would like to know by that button what mode I am in when I start up, without having to press the dang thing. I have tried various routines to run on workbook open, activate, etc... I try reading the current calculation mode and setting the button caption accordingly. The routines work fine when I test them with Excel already up and running, so I am fairly confident the code works, but it still displays improperly when I first start Excel. Perhaps it is timing - that I am trying to read the calc mode when it is still in its default setting, before applying any saved settings? I still can't figure it out. Anyone out there who can help? Here's the code to toggle the mode and set the caption - I have it in my Personal.xls workbook: Sub CalcMode() Dim CurrentMode As Integer CurrentMode = Application.Calculation Select Case CurrentMode Case xlCalculationAutomatic Application.Calculation = xlCalculationManual Case xlCalculationManual Application.Calculation = xlCalculationSemiautomatic Case xlCalculationSemiautomatic Application.Calculation = xlCalculationAutomatic Case Else Application.Calculation = xlCalculationAutomatic End Select CurrentMode = Application.Calculation Select Case CurrentMode Case xlCalculationAutomatic Application.CommandBars("Calculation").Controls (1).Caption = "AUTO" Case xlCalculationManual Application.CommandBars("Calculation").Controls (1).Caption = "MANUAL" Case xlCalculationSemiautomatic Application.CommandBars("Calculation").Controls (1).Caption = "SEMI" Case Else Application.CommandBars("Calculation").Controls (1).Caption = "???" End Select End Sub Thanks in advance... K Dales |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation mode
Thanks Bob. I am using a manually created toolbar that is
saved as part of my Personal.xls workbook. I have tried getting the code to run when the workbook opens to read the mode and set the caption accordingly but it the code always seems to capture the default state (AUTO) instead of the saved state. Maybe this is because the saved state is not read by Excel until after the Workbook_Open and Workbook_Activate events? I don't know, that seems to be where I run into trouble. I just don't know whcih event I can tie my code to in order to read the saved state properly and set that caption. -----Original Message----- Do you have code to create the toolbar as you open the workbook, or do you just have a manually built toolbar? If the former, you need to run the test to set the caption accordingly, if the latter then it's probably because the toolbar saves an initial state. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "K Dales" wrote in message ... I have created a custom "Calculation" toolbar that gives me the functions "Calc Sheet", "Calc Full", and "Calc Now." I also added a button that uses VBA code to toggle between Manual, Automatic, and Semiautomatic calc modes. The code also changes the caption on the button to read "MANUAL", "AUTO" or "SEMI" depending on the currently selected mode. So far so good... The problem: When I start up Excel, the caption on my button does not necessarily reflect the current calculation mode. It always comes up as "AUTO." As soon as I press the button it reads the mode and the caption changes appropriately, but I sure would like to know by that button what mode I am in when I start up, without having to press the dang thing. I have tried various routines to run on workbook open, activate, etc... I try reading the current calculation mode and setting the button caption accordingly. The routines work fine when I test them with Excel already up and running, so I am fairly confident the code works, but it still displays improperly when I first start Excel. Perhaps it is timing - that I am trying to read the calc mode when it is still in its default setting, before applying any saved settings? I still can't figure it out. Anyone out there who can help? Here's the code to toggle the mode and set the caption - I have it in my Personal.xls workbook: Sub CalcMode() Dim CurrentMode As Integer CurrentMode = Application.Calculation Select Case CurrentMode Case xlCalculationAutomatic Application.Calculation = xlCalculationManual Case xlCalculationManual Application.Calculation = xlCalculationSemiautomatic Case xlCalculationSemiautomatic Application.Calculation = xlCalculationAutomatic Case Else Application.Calculation = xlCalculationAutomatic End Select CurrentMode = Application.Calculation Select Case CurrentMode Case xlCalculationAutomatic Application.CommandBars("Calculation").Controls (1).Caption = "AUTO" Case xlCalculationManual Application.CommandBars("Calculation").Controls (1).Caption = "MANUAL" Case xlCalculationSemiautomatic Application.CommandBars("Calculation").Controls (1).Caption = "SEMI" Case Else Application.CommandBars("Calculation").Controls (1).Caption = "???" End Select End Sub Thanks in advance... K Dales . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation mode
-- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "K Dales" wrote in message ... Thanks Bob. I am using a manually created toolbar that is saved as part of my Personal.xls workbook. I have tried getting the code to run when the workbook opens to read the mode and set the caption accordingly but it the code always seems to capture the default state (AUTO) instead of the saved state. Maybe this is because the saved state is not read by Excel until after the Workbook_Open and Workbook_Activate events? I don't know, that seems to be where I run into trouble. I just don't know whcih event I can tie my code to in order to read the saved state properly and set that caption. -----Original Message----- Do you have code to create the toolbar as you open the workbook, or do you just have a manually built toolbar? If the former, you need to run the test to set the caption accordingly, if the latter then it's probably because the toolbar saves an initial state. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "K Dales" wrote in message ... I have created a custom "Calculation" toolbar that gives me the functions "Calc Sheet", "Calc Full", and "Calc Now." I also added a button that uses VBA code to toggle between Manual, Automatic, and Semiautomatic calc modes. The code also changes the caption on the button to read "MANUAL", "AUTO" or "SEMI" depending on the currently selected mode. So far so good... The problem: When I start up Excel, the caption on my button does not necessarily reflect the current calculation mode. It always comes up as "AUTO." As soon as I press the button it reads the mode and the caption changes appropriately, but I sure would like to know by that button what mode I am in when I start up, without having to press the dang thing. I have tried various routines to run on workbook open, activate, etc... I try reading the current calculation mode and setting the button caption accordingly. The routines work fine when I test them with Excel already up and running, so I am fairly confident the code works, but it still displays improperly when I first start Excel. Perhaps it is timing - that I am trying to read the calc mode when it is still in its default setting, before applying any saved settings? I still can't figure it out. Anyone out there who can help? Here's the code to toggle the mode and set the caption - I have it in my Personal.xls workbook: Sub CalcMode() Dim CurrentMode As Integer CurrentMode = Application.Calculation Select Case CurrentMode Case xlCalculationAutomatic Application.Calculation = xlCalculationManual Case xlCalculationManual Application.Calculation = xlCalculationSemiautomatic Case xlCalculationSemiautomatic Application.Calculation = xlCalculationAutomatic Case Else Application.Calculation = xlCalculationAutomatic End Select CurrentMode = Application.Calculation Select Case CurrentMode Case xlCalculationAutomatic Application.CommandBars("Calculation").Controls (1).Caption = "AUTO" Case xlCalculationManual Application.CommandBars("Calculation").Controls (1).Caption = "MANUAL" Case xlCalculationSemiautomatic Application.CommandBars("Calculation").Controls (1).Caption = "SEMI" Case Else Application.CommandBars("Calculation").Controls (1).Caption = "???" End Select End Sub Thanks in advance... K Dales . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation mode
I think your problem stems from the fact that Calculate is an application
property, not workbook, and it takes the state of the first workbook open ed. So if you are opening this particular workbook after this, it's state will assume that of the first workbook opened. If you want to override this, you will need to save it with the workbook, and read it when you open the workbook and set accordingly. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "K Dales" wrote in message ... Thanks Bob. I am using a manually created toolbar that is saved as part of my Personal.xls workbook. I have tried getting the code to run when the workbook opens to read the mode and set the caption accordingly but it the code always seems to capture the default state (AUTO) instead of the saved state. Maybe this is because the saved state is not read by Excel until after the Workbook_Open and Workbook_Activate events? I don't know, that seems to be where I run into trouble. I just don't know whcih event I can tie my code to in order to read the saved state properly and set that caption. -----Original Message----- Do you have code to create the toolbar as you open the workbook, or do you just have a manually built toolbar? If the former, you need to run the test to set the caption accordingly, if the latter then it's probably because the toolbar saves an initial state. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "K Dales" wrote in message ... I have created a custom "Calculation" toolbar that gives me the functions "Calc Sheet", "Calc Full", and "Calc Now." I also added a button that uses VBA code to toggle between Manual, Automatic, and Semiautomatic calc modes. The code also changes the caption on the button to read "MANUAL", "AUTO" or "SEMI" depending on the currently selected mode. So far so good... The problem: When I start up Excel, the caption on my button does not necessarily reflect the current calculation mode. It always comes up as "AUTO." As soon as I press the button it reads the mode and the caption changes appropriately, but I sure would like to know by that button what mode I am in when I start up, without having to press the dang thing. I have tried various routines to run on workbook open, activate, etc... I try reading the current calculation mode and setting the button caption accordingly. The routines work fine when I test them with Excel already up and running, so I am fairly confident the code works, but it still displays improperly when I first start Excel. Perhaps it is timing - that I am trying to read the calc mode when it is still in its default setting, before applying any saved settings? I still can't figure it out. Anyone out there who can help? Here's the code to toggle the mode and set the caption - I have it in my Personal.xls workbook: Sub CalcMode() Dim CurrentMode As Integer CurrentMode = Application.Calculation Select Case CurrentMode Case xlCalculationAutomatic Application.Calculation = xlCalculationManual Case xlCalculationManual Application.Calculation = xlCalculationSemiautomatic Case xlCalculationSemiautomatic Application.Calculation = xlCalculationAutomatic Case Else Application.Calculation = xlCalculationAutomatic End Select CurrentMode = Application.Calculation Select Case CurrentMode Case xlCalculationAutomatic Application.CommandBars("Calculation").Controls (1).Caption = "AUTO" Case xlCalculationManual Application.CommandBars("Calculation").Controls (1).Caption = "MANUAL" Case xlCalculationSemiautomatic Application.CommandBars("Calculation").Controls (1).Caption = "SEMI" Case Else Application.CommandBars("Calculation").Controls (1).Caption = "???" End Select End Sub Thanks in advance... K Dales . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prevent Manual Calculation Mode | Excel Discussion (Misc queries) | |||
Selective Calculation of Mean & Mode | Excel Worksheet Functions | |||
manual calculation mode | Excel Discussion (Misc queries) | |||
Ommitting 0 from a mode calculation. | Excel Discussion (Misc queries) | |||
My Calculation Mode Changed to Manual Somehow?!? | Excel Discussion (Misc queries) |