Detecting Calc On or Off

  • Thread starter Thread starter dhstein
  • Start date Start date
D

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.
 
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
 
forgot to add:

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

then use yr "Calc On"/"Calc Off" button
 
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
 
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
 
Back
Top