wal wrote on 3/29/2011 :
Why are you playing around with the button state?

Other posts seem to suggest it would work or be appropriate.

Besides, how do you expect to restore calc mode when the button is down
(and so can't be clicked)?

Isn't .State what causes a button to be "dark" vs. not? And doesn't
"dark" = down still allow the button to be pressed? For example, if
you run the following code for the Bold button, when the button is
"light" = up (and the text in the cell not bold), the result is 0 =
msoButtonUp; if the button is "dark" = down (cell text is bold), the
result is -1 = msoButtonDown and the button still works to toggle the
bold attribute.

Dim myBar As CommandBar, myControl As CommandBarButton
Set myBar = CommandBars("myFormatting")
Set myControl = myBar.Controls("Bold")
MsgBox myControl.State

Thanks for the sample code, which I'll try later. (I'm still curious
as to why the button can't be made to become dark by setting
the .State.)



On Mar 28, 10:09*pm, GS wrote:
After serious thinking wal wrote :





Excel 2003

I created a button using Right-click-on-control-bars Customize, and
assigned the following macro to it:

Sub ToggleCalculation()

Dim myBar As CommandBar, myControl As CommandBarButton
Set myBar = CommandBars("myMacros")
Set myControl = myBar.Controls("ToggleCalculation")
If Application.Calculation = xlCalculationAutomatic Then
* * Application.Calculation = xlCalculationManual
* * myControl.State = msoButtonUp
ElseIf Application.Calculation = xlCalculationManual Then
* * Application.Calculation = xlCalculationAutomatic
* * myControl.State = msoButtonDown
End If

MsgBox myControl.State
End Sub

When I press the button, the calculation setting does toggle. *But the
button stays in the "up" position (.State = 0).
Any ideas? *Thanks.

Why are you playing around with the button state? I suspect you're
trying to hint whether calculation is on or the other. It would be
easier to change the caption so there's no ambiguity which mode
calculation is in without messing around with the button state.
Besides, how do you expect to restore calc mode when the button is down
(and so can't be clicked)?

Try this...

Sub ToggleCalculation()
* Select Case Application.Calculation
* * Case xlCalculationAutomatic
* * * Application.Calculation = xlCalculationManual
* * * CommandBars.ActionControl.Caption = "Set Automatic Calculation"
* * Case xlCalculationManual
* * * Application.Calculation = xlCalculationAutomatic
* * * CommandBars.ActionControl.Caption = "Set Manual Calculation"
* End Select
End Sub

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

Good luck with this!

Just in case you go with my suggestion to change the caption, please
know that the only way you can set the Tag prop for a menuitem created
via the 'Customize' dialog is to do it in the VBE. You can use the
Immediate Window, and the following statement:


CommandBars("myMacros").Controls("ToggleCalculatio n").Tag="ToggleCalculation"

Do this before initial running of the startup code I posted.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc