Detecting Calc On or Off

[dhstein]

I have 2 radio buttons - one marked "Calc On" and one marked "Calc Off".
The user can turn auto calc on or off. There are other macros in the
spreadsheet - and I also turn calc off during those macros and then turn it
back on. What I want is to be able to detect what the current setting is and
then set it to that at the end of macro execution. So if Calc is "OFF" then
at the end of the macro I want to leave it off. Any advice is appreciated.

 

[Jarek Kujawa]

one way might be to use this function:

Function calc()

If Application.Calculation = xlCalculationManual Then
calc = "Manual"
ElseIf Application.Calculation = xlCalculationAutomatic Then
calc = "Automatic"
Else
calc = "SemiAutomatic"
End If

End Function


and then use 1 of existing buttons to change the setting

to insert this function press ALT+F11 to go Visual Basic window, then
Insert->Module and paste this code

otherwise you would have to change all existing macros to reflect the
current calculation setting

 

[Jarek Kujawa]

forgot to add:

insert =calc() into any unused cell to determine the current setting

then use yr "Calc On"/"Calc Off" button

 

[dhstein]

Jarek,

Thanks for the reply, but the test doesn't seem to work. I checked and did
a MsgBox on Application.Calculation and the result always comes back as -4135
no matter what state the calc is in.

David

 

[Jarek Kujawa]

what I meant was:

1. use Sub Licz() (below) to determine the current setting
2. use any of your macros
3. change the setting with your "Calc On"/"Calc Off" buttons


Sub licz()

If Application.Calculation = xlCalculationManual Then
calc = "Manual"
ElseIf Application.Calculation = xlCalculationAutomatic Then
calc = "Automatic"
Else
calc = "SemiAutomatic"
End If

MsgBox calc
End Sub


works on my Excel 2007

 

[dhstein]

Thanks Jarek - it's working now - I'm not sure what I did wrong the first
time.