CODEWITHSUNDEEP
vbaexcelribbon
Chris Shaw
Chris Shaw

Reputation: 19

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?

Upvotes: 2

Views: 5255

Answers (2)

phmauber
phmauber

Reputation: 31

sorry, I just found it. Callbacks for xml lines <command... etc. > have two arguments and must have the following syntax:

'Callback for HyperlinkInsert onAction
Sub Test(control As IRibbonControl, ByRef cancelDefault)
End Sub

With this syntax no more problem... and to think that I've been on it for more than two hours and my searches had yielded nothing

Upvotes: 0

aduguid
aduguid

Reputation: 3195

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

Upvotes: 4

Related Questions