Microsoft Office Forums - View Single Post - Pass sheet protected argument to Ribbon Control

BartH · 03-20-2018, 02:08 AM

Hi All,

I have put a toggle button on my Excel ribbon (customUI) to display and change the Sheet Protect status. When the control is clicked, Sheet Protecting for the active sheet is turned On or Off, depending on the current ProtectContents state.
This works fine, also for the onLoad event of the Ribbon, but I also would need the Ribbon control to detect whether a sheet is protected or not when I select or open another workbook/sheet.

How could I detect the ProtectContents state of any sheet (without having to put code in the worksheet.sheetActivate event - as it should also work for foreign files I have no control over) and pass it back to the toggle control, to make it reflect the current Protection status as pressed or not pressed:

Code for the ribbon control:

Code:

Option Explicit

Public gbProtectionState As Boolean
Public MyRibbon As IRibbonUI

'Callback for customUI.******
Sub ToggleProtection(ribbon As IRibbonUI)
On Error Resume Next
    Set MyRibbon = ribbon
    '--read previously saved value of toggle
    gbProtectionState = ActiveWorkbook.ActiveSheet.ProtectContents
    'test if onLoad works
    MsgBox "MyTab Loaded"
    'Should I use this - so far no difference when I test it...
    MyRibbon.InvalidateControl "TbtnToggleProtection"
End Sub

'Callback for TbtnToggleProtection onAction
Sub TbtnToggleProtectionIsClicked(control As IRibbonControl, pressed As Boolean)
    'call procedure to protect/unprotect sheet
    Call doProtectSheet
    'load sheet Protection status to global variable
    Call ChangeProtectionState
End Sub

'Get Protection state of current sheet
Private Sub ChangeProtectionState()
    gbProtectionState = ActiveWorkbook.ActiveSheet.ProtectContents
End Sub

'Callback for TbtnToggleProtection getPressed
Sub GetPPressed(control As IRibbonControl, ByRef returnedVal)
    returnedVal = gbProtectionState
End Sub

XML in customUI:

Code:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="ToggleProtection" >
	<ribbon>
		<tabs>
			<tab id="MyTab" label="MyTab" insertAfterMso="TabHome">
				<group id="customGroup" label="Cell/Protection Tools" >
                    			<toggleButton id="TbtnToggleProtection" 
                        			label="Sheet Protection"
                        			screentip="Turn sheet protection On or Off" 
                        			size="large" 
                        			onAction="TbtnToggleProtectionIsClicked" 
                        			getPressed="GetPPressed" 
                        			image="Protect"/>
				</group>
			</tab>
		</tabs>
	</ribbon>
</customUI>

Any help much appreciated, GrtZ BartH