Problem setting the State of a custom Toolbar button

[Paul Martin]

I have created a number of custom toolbar buttons on a custom
toolbar. Some of these are toggle buttons and I can set their state
(ie, msoButtonDown or msoButtonUp). One of these, however, is not
responding and the State doesn't change. The code is below in full,
and any suggestions are appreciated.

Thanks in advance

Paul Martin
Melbourne, Australia


Public Sub ToggleCalcMode()
With Application
.Calculation = IIf(.Calculation = xlCalculationManual, _
xlCalculationAutomatic,
xlCalculationManual)
MsgBox "Excel is in " & IIf(.Calculation =
xlCalculationManual, "Manual", "Automatic") _
& " calc mode"

CommandBars(CUSTOM_TOOLBAR).Controls("Toggle Calculation
Mode").State = _
IIf(.Calculation = xlCalculationAutomatic, msoButtonDown,
msoButtonUp)
End With
End Sub

 

[Paul Martin]

Does anyone have any suggestions regarding this issue?

 

[Peter T]

Assuming your button can show a difference in state (it depends on type &
location) I don't see anything obvious in your code why the state does not
change (I may have missed). Try something like this instead -

Sub ToggleCalc()
Dim nCalc As Long
Dim nState As Long
Dim sCalc
Dim cb As CommandBarButton

' Set cb = CommandBars(CUSTOM_TOOLBAR). _
Controls("Toggle Calculation Mode")

Set cb = Application.CommandBars.ActionControl ' for testing

On Error GoTo errH
nCalc = Application.Calculation

Select Case nCalc
Case xlCalculationManual, xlCalculationSemiautomatic
nCalc = xlCalculationAutomatic
sCalc = "Automatic"
nState = msoButtonDown
Case Else
nCalc = xlCalculationManual
sCalc = "Manual"
nState = msoButtonUp
End Select

Application.Calculation = nCalc
cb.State = nState
MsgBox "Calculation is now " & sCalc

Exit Sub
errH:
MsgBox "Cannot change Calculation" ' eg no activeworkbook

End Sub

Regards,
Peter T

 

[Paul Martin]

Thanks for the attempt, Peter, but your suggested code doesn't change
the button's state either. I did, however, find the cause of the
problem and the solutions.

I used the calculator icon, by clicking, Tools, Customise, Commands,
Tools and dragging Custom (with the calculator icon) onto my custom
toolbar. This icon doesn't seem to like having its state changed by
my code.

I solved the problem by putting my own custom icon there and in code
changing it's FaceID to 283 (which is the same icon). This button now
changes state as expected.

Hopefully this solution might help others down the track.

Regards

Paul

 

[Peter T]

So if I follow, what I said about "assuming your button can show a
difference in state etc" turned out to be rather pertinent!

Regards,
Peter T