I'm trying to make it so that a custom button is disabled depending on the toggle state of another custom button on the Excel ribbon. I have a button defined in my ribbons' XML as follows:
<button id="ButtonCalc" label="Re-Calculate" size="large" onAction="calc" image="Calculate" getEnabled="disableRefresh"/>
The call-back for the getEnabled tag is:
'Callback for ButtonCalc getEnabled
Sub disableRefresh(control As IRibbonControl, ByRef enabled)
If calcState Then
enabled = True
Else
enabled = False
End If
End Sub
The term "calcstate" is a boolean operation controlled by the toggle button:
<toggleButton id="ButtonMode" label="Light Mode" size="large" onAction="ToggleCalcClicked" image="Mode" getPressed="checkLight"/>
VBA for this part:
Public calcState As Boolean
Dim MyRibbon As IRibbonUI
Sub ToggleCalcClicked(control As IRibbonControl, pressed As Boolean)
calcState = Not calcState
Worksheets("Setup").Range("L47").Value = calcState
Call ChangeCalcState
End Sub
Everything works, except the button for Re-calculate is permanently disabled. What have I done wrong?
You have to invalidate the control to change the visibility or enabled properties.
Public calcState As Boolean
Dim MyRibbon As IRibbonUI
Sub ToggleCalcClicked(control As IRibbonControl, pressed As Boolean)
calcState = Not calcState
Worksheets("Setup").Range("L47").Value = calcState
ChangeCalcState
MyRibbon.InvalidateControl("ButtonCalc")
End Sub