Pass sheet protected argument to Ribbon Control

BartH · #1 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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Pass Arguments to Function Through Ribbon	inagalaxyfarfarawry	Word VBA	11	03-25-2022 09:28 PM
Errors in macro when sheet protected	ernief	Excel Programming	2	11-18-2016 03:35 AM
Using combobox to pass data from an Excel spread sheet to text boxes on userform	Stewie840	Word VBA	14	12-17-2015 10:13 PM
creating a content control that is password protected	kawtharz	Word VBA	5	08-21-2015 12:50 AM
Autoexpand Table on Protected Sheet	Catalin.B	Excel	0	09-07-2014 07:03 AM