Button on toolbar for AutoCalc

[Conan Kelly]

Hello All,

I have a button on a custom toolbar to change the settings of the
calculation mode of Excel (Tools>Options...>Calculation
Tab>Calculation Settings):

Sub ToggleAutoCalc()
If Application.Calculation = xlCalculationAutomatic Then
Application.Calculation = xlCalculationManual
Application.CalculateBeforeSave = False
Else
Application.Calculation = xlCalculationAutomatic
Application.CalculateBeforeSave = True
End If

End Sub

The problem I am having is that my button doesn't tell me if it is on
or off (the Name property of the button is set to "Toggle AutoCalc").
I want to change it to read "AutoCalc On" and "AutoCalc Off". I can
figure out the code to change the text displayed, but I also want to
check the status of the settings each time a new file is opened (I
open Excel in the morning and try to leave it open the whole day,
opening and closing several files throughout the day).

Which event procedure should I put this in? There isn't an
"Application" project object in the Project Explorer. Should it go in
"Personal.xls"? If so, which event procedure does it need to go in so
it will check each time a different file is opened?

Thanks for any help anyone can provide,

Conan Kelly

 

[Jim Rech]

You would need to use Application level events. Chip Pearson discusses them
here:

http://www.cpearson.com/excel/AppEvent.htm

and his download contains an example of using the Application Workbook Open
event, which is the one you would want to use.

--
Jim
"Conan Kelly" <CTBarbarin at msn dot com> wrote in message
| Hello All,
|
| I have a button on a custom toolbar to change the settings of the
| calculation mode of Excel (Tools>Options...>Calculation
| Tab>Calculation Settings):
|
| Sub ToggleAutoCalc()
| If Application.Calculation = xlCalculationAutomatic Then
| Application.Calculation = xlCalculationManual
| Application.CalculateBeforeSave = False
| Else
| Application.Calculation = xlCalculationAutomatic
| Application.CalculateBeforeSave = True
| End If
|
| End Sub
|
| The problem I am having is that my button doesn't tell me if it is on
| or off (the Name property of the button is set to "Toggle AutoCalc").
| I want to change it to read "AutoCalc On" and "AutoCalc Off". I can
| figure out the code to change the text displayed, but I also want to
| check the status of the settings each time a new file is opened (I
| open Excel in the morning and try to leave it open the whole day,
| opening and closing several files throughout the day).
|
| Which event procedure should I put this in? There isn't an
| "Application" project object in the Project Explorer. Should it go in
| "Personal.xls"? If so, which event procedure does it need to go in so
| it will check each time a different file is opened?
|
| Thanks for any help anyone can provide,
|
| Conan Kelly
|
|

 

[Conan Kelly]

Jim Rech (and Chip Pearson),

Thank you so much for all of your help. This is exactly what I was
looking for. It worked perfectly.

Thanks again,

Conan Kelly