View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
wal wal is offline
external usenet poster
 
Posts: 8
Default Can't get Control.State to work

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.