Calculation Setting

[Paige]

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!

 

[John Bundy]

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 Bundy]

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

 

[Paige]

Thanks, John!!!

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

 

[Martin Fishlock]

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.

 

[NickHK]

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

 

[PBezucha]

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

 

[Paige]

Thanks everyone for all the great suggestions. Will give them a try today.
Again, appreciate it very much!