vbaexcelribbon

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