Montag, 7. Mai 2012 09:30
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:
<command idMso="SheetRowsInsert" onAction="ThisWorkbook.myInsertRows"/>
<command idMso="SheetRowsDelete" getEnabled="ThisWorkBook.myDeleteEnable" onAction="ThisWorkbook.myDeleteRows"/>
Sub myDeleteEnable(control As IRibbonControl, ByRef enabled)
Debug.Print "-> myDeleteEnabled: " & control.ID
If control.ID = "SheetRowsDelete" Then
enabled = True
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.
.ScreenUpdating = True
.DisplayAlerts = True
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.
Montag, 7. Mai 2012 10:41
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
- Als Antwort markiert JohnEd Montag, 7. Mai 2012 18:23
Montag, 7. Mai 2012 18:22
Thank you for the very quick reply.
That's too bad. So I guess I'm going to have to build my own group.
- Bearbeitet JohnEd Montag, 7. Mai 2012 18:23