How to enable a repurposed Ribbon button.

Question

I'm trying to repurpose the SheetRowsDelete ribbon control. When it's enabled by Excel, I've managed to get the control to call my onAction routine. The problem is that the command is disabled when the sheet is protected. How can I enable the command when he sheet is protected? Here's the relevant code:

XML:

  <commands>
    <command idMso="SheetRowsInsert" onAction="ThisWorkbook.myInsertRows"/>
    <command idMso="SheetRowsDelete" getEnabled="ThisWorkBook.myDeleteEnable" onAction="ThisWorkbook.myDeleteRows"/>
  </commands>

VBA:

Sub myDeleteEnable(control As IRibbonControl, ByRef enabled)
Debug.Print "-> myDeleteEnabled: " & control.ID
If control.ID = "SheetRowsDelete" Then
    enabled = True
End If
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

'Other code not relevant to this discussion.

' Do this to cause the myDeleteEnable callback to be executed.
MyRibbon.InvalidateControlMso ("SheetRowsDelete")
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
End Sub

This seems to work because the call to myDeleteEnable is being executed and the If statement check is Tue. I've also tried to put in the enable="true" in place of the getEnabled callback. But still no luck.

Is there a way to enable this control even though the sheet is protected.

Thanks,

John

Answer 1

Hi John,

If you repurpose built-in controls, the enabling and disabling is handled by Excel. There is no way to change that as far asa I know.

In order to have full control, you must add your own custom button to a custom tab and/or group. Then you can use the getEnabled callback to have your code handle the enable/disable process.

---

Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com

Answer 2

Thank you for the very quick reply.

That's too bad. So I guess I'm going to have to build my own group.