Hello All,

I have a button on a custom toolbar to change the settings of the
calculation mode of Excel (ToolsOptions...Calculation
TabCalculation 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

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

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 (ToolsOptions...Calculation
| TabCalculation 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 (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




"Jim Rech" wrote in message
...
You would need to use Application level events. Chip Pearson
discusses them
he

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 (ToolsOptions...Calculation
| TabCalculation 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
|
|