Detecting Calc On or Off
 

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.

Detecting Calc On or Off
 

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


On 21 Kwi, 10:48, dhstein wrote:
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.

Detecting Calc On or Off
 

forgot to add:

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

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


On 21 Kwi, 11:21, Jarek Kujawa wrote:
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

On 21 Kwi, 10:48, dhstein wrote:



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.- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -

Detecting Calc On or Off
 

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" wrote:

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


On 21 Kwi, 10:48, dhstein wrote:
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.

Detecting Calc On or Off
 

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


On 21 Kwi, 14:09, dhstein wrote:
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" wrote:
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

On 21 Kwi, 10:48, dhstein wrote:
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.- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

Detecting Calc On or Off
 

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

"Jarek Kujawa" wrote:

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


On 21 Kwi, 14:09, dhstein wrote:
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" wrote:
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

On 21 Kwi, 10:48, dhstein wrote:
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.- Ukryj cytowany tekst -

- Pokaż cytowany tekst -