Peter T brought next idea :
Should work, the "State" should remain as set, try this
Sub delBar()
' run this after testing to clean up
On Error Resume Next
CommandBars("testBar").Delete
End Sub
Sub addBar()
Dim cbr As CommandBar, cbt As CommandBarButton
delBar
Set cbr = CommandBars.Add("testBar", , , True)
cbr.Visible = True
Set cbt = cbr.Controls.Add(1)
With cbt
.Caption = "My Macro"
.OnAction = "MyMacro"
.Style = msoButtonCaption
End With
End Sub
Sub myMacro()
Dim cbt As CommandBarButton
Static b As Boolean
b = Not b
Set cbt = CommandBars.ActionControl
cbt.Caption = "My Macro " & b
cbt.State = b
End Sub
The way State is indicated differs, might be change in colour or a tick to
the left.
(This is not useful for 2007/2010 users unless used on a popup bar)
Regards,
Peter T
"wal" wrote in message
...
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.
I suspect the OP is using a custom toolbar created via the 'Customize'
dialog. If so then setting 'State' has no effect, though no error is
generated by attempting to set this property.
Attempting to do same with any of the built-in commandbars generates an
'automation' error.
--
Garry
Free usenet access at
http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc