Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation Setting
I need to have a cell that tells the user what the current state of
calculation is (i.e., is it set to automatic or manual); this would have to change automatically whenever calculation is changed from auto to manual or vice versa. The workbook is normally set to auto, but the user can change the status manually back and forth. Where/how can I insert code that would be triggered to update whenever calc is changed, and look to see what it was changed to? Tried putting some code under various worksheet and workbook events, but keep getting those pesky little 'out of stack space' messages! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation Setting
Try this out
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Application.Calculation = xlCalculationAutomatic Then Cells(1, 1) = "Auto" If Application.Calculation = xlCalculationManual Then Cells(1, 1) = "Manual" End Sub -- -John Northwest11 Please rate when your question is answered to help us and others know what is helpful. "Paige" wrote: I need to have a cell that tells the user what the current state of calculation is (i.e., is it set to automatic or manual); this would have to change automatically whenever calculation is changed from auto to manual or vice versa. The workbook is normally set to auto, but the user can change the status manually back and forth. Where/how can I insert code that would be triggered to update whenever calc is changed, and look to see what it was changed to? Tried putting some code under various worksheet and workbook events, but keep getting those pesky little 'out of stack space' messages! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation Setting
Here you go for the whole workbook
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) If Application.Calculation = xlCalculationAutomatic Then ActiveSheet.Cells(1, 1) = "Auto" If Application.Calculation = xlCalculationManual Then ActiveSheet.Cells(1, 1) = "Manual" End Sub -- -John Northwest11 Please rate when your question is answered to help us and others know what is helpful. "Paige" wrote: I need to have a cell that tells the user what the current state of calculation is (i.e., is it set to automatic or manual); this would have to change automatically whenever calculation is changed from auto to manual or vice versa. The workbook is normally set to auto, but the user can change the status manually back and forth. Where/how can I insert code that would be triggered to update whenever calc is changed, and look to see what it was changed to? Tried putting some code under various worksheet and workbook events, but keep getting those pesky little 'out of stack space' messages! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation Setting
Thanks, John!!!
"John Bundy" wrote: Here you go for the whole workbook Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) If Application.Calculation = xlCalculationAutomatic Then ActiveSheet.Cells(1, 1) = "Auto" If Application.Calculation = xlCalculationManual Then ActiveSheet.Cells(1, 1) = "Manual" End Sub -- -John Northwest11 Please rate when your question is answered to help us and others know what is helpful. "Paige" wrote: I need to have a cell that tells the user what the current state of calculation is (i.e., is it set to automatic or manual); this would have to change automatically whenever calculation is changed from auto to manual or vice versa. The workbook is normally set to auto, but the user can change the status manually back and forth. Where/how can I insert code that would be triggered to update whenever calc is changed, and look to see what it was changed to? Tried putting some code under various worksheet and workbook events, but keep getting those pesky little 'out of stack space' messages! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation Setting
Hi Paige:
The following works for auto and semi but not for a switch to manual (becasue the manual does not recalculate the sheet). Function Status1() Application.Volatile Select Case Application.Calculation Case xlCalculationManual Status1 = "Manual" Case xlCalculationSemiautomatic Status1 = "Semi" Case xlCalculationAutomatic Status1 = "Auto" End Select End Function You need to therefore try an on timer event unless somebody can come up with an alternative. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Paige" wrote: I need to have a cell that tells the user what the current state of calculation is (i.e., is it set to automatic or manual); this would have to change automatically whenever calculation is changed from auto to manual or vice versa. The workbook is normally set to auto, but the user can change the status manually back and forth. Where/how can I insert code that would be triggered to update whenever calc is changed, and look to see what it was changed to? Tried putting some code under various worksheet and workbook events, but keep getting those pesky little 'out of stack space' messages! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation Setting
You can use the older Macro4 call.
Create a new name, maybe "CalcMode" and set it "=GET.DOCUMENT(14)" Then in you worksheet you can enter "=CalcMode" whereever you need it. You can enhance the formula to to return a string instead if desired. NickHK "Paige" wrote in message ... I need to have a cell that tells the user what the current state of calculation is (i.e., is it set to automatic or manual); this would have to change automatically whenever calculation is changed from auto to manual or vice versa. The workbook is normally set to auto, but the user can change the status manually back and forth. Where/how can I insert code that would be triggered to update whenever calc is changed, and look to see what it was changed to? Tried putting some code under various worksheet and workbook events, but keep getting those pesky little 'out of stack space' messages! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation Setting
Paige,
What about toggling the calculation mode with the notice in StatusBar? A linked two-way short-key would be useful. Private Sub AutoManuCalculation() With Application If .Calculation = xlCalculationAutomatic Then .Calculation = xlCalculationManual .StatusBar = "ATTENTION Manual calculation!" Else .StatusBar = False .Calculation = xlCalculationAutomatic End If End With End Sub -- Petr Bezucha "Paige" wrote: Thanks, John!!! "John Bundy" wrote: Here you go for the whole workbook Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) If Application.Calculation = xlCalculationAutomatic Then ActiveSheet.Cells(1, 1) = "Auto" If Application.Calculation = xlCalculationManual Then ActiveSheet.Cells(1, 1) = "Manual" End Sub -- -John Northwest11 Please rate when your question is answered to help us and others know what is helpful. "Paige" wrote: I need to have a cell that tells the user what the current state of calculation is (i.e., is it set to automatic or manual); this would have to change automatically whenever calculation is changed from auto to manual or vice versa. The workbook is normally set to auto, but the user can change the status manually back and forth. Where/how can I insert code that would be triggered to update whenever calc is changed, and look to see what it was changed to? Tried putting some code under various worksheet and workbook events, but keep getting those pesky little 'out of stack space' messages! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation Setting
Thanks everyone for all the great suggestions. Will give them a try today.
Again, appreciate it very much! "NickHK" wrote: You can use the older Macro4 call. Create a new name, maybe "CalcMode" and set it "=GET.DOCUMENT(14)" Then in you worksheet you can enter "=CalcMode" whereever you need it. You can enhance the formula to to return a string instead if desired. NickHK "Paige" wrote in message ... I need to have a cell that tells the user what the current state of calculation is (i.e., is it set to automatic or manual); this would have to change automatically whenever calculation is changed from auto to manual or vice versa. The workbook is normally set to auto, but the user can change the status manually back and forth. Where/how can I insert code that would be triggered to update whenever calc is changed, and look to see what it was changed to? Tried putting some code under various worksheet and workbook events, but keep getting those pesky little 'out of stack space' messages! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Manual calculation setting | Excel Discussion (Misc queries) | |||
permenantly uncheck calculation setting. | Excel Discussion (Misc queries) | |||
how to determine calculation setting? | Excel Discussion (Misc queries) | |||
Calculation Setting in Excel | Excel Discussion (Misc queries) | |||
Auto Calculation Setting | Excel Discussion (Misc queries) |