[SOLVED] Disable Ribbon button in Excel

Disable Ribbon button in Excel

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?

Solution

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