Hide Protect/Unprotect Sheet

[pcsparky]

Using the following code in the Thisworkbook Object I did manage to prevent users from using the Unprotect or Protect Workbook options in Excel 2003:

Application.CommandBars("Tools").Controls("Protection").Enabled = False

My workplace has now upgraded to Excel 2007. Can I change the code to work with Excel 2007?

[Bob Phillips]

You have to add an item to the ribbon for that control to control its visibility property,



<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<customUI onLoad="ribbonLoaded" xmlns="http://schemas.microsoft.com/office/2006/01/customui">

<ribbon>
<tabs>

<!-- hide/display the Protect Sheet control -->
<group idMso="Sheet Protect" getVisible="GetVisible" />

</tab>
</tabs>
</ribbon>

</customUI>


and then have some code to make it visible/not visible.



Dim Rib As IRibbonUI
Public Enabled_Visible As Boolean
'

'Callback for customUI.onLoad
Sub ribbonLoaded(ribbon As IRibbonUI)
Set Rib = ribbon
End Sub

'Callbacks to Hide Disable or Display Enable Ribbon controls/groups
Sub GetVisible(control As IRibbonControl, ByRef returnedVal)
returnedVal = Enabled_Visible
End Sub

Sub Set_Enabled_Visible(Vis As Boolean)
Enabled_Visible = Vis
'To Reload the RibbonX of one group/control use
Rib.Invalidate
End Sub


and then you need so code to trigger the display/hide.

[pcsparky]

Wow! You surely are the Lord of VBA but I'm afraid the HTML went right over my head. Where should I put that code please?

[Bob Phillips]

It is not HTML, it is XML.

I just ripped that off the top of my head (aftere a quick check on one of my old projects), so it may not be 100%. But you should familiarise yourself with building ribbons in 2007 first, it is not trivial and certainly not intuitive.

I suggest that you start at http://www.excelguru.ca/blog/2006/12/01/ribbon-example-table-of-contents/