Pass sheet protected argument to Ribbon Control

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:

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
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

Open in new window


XML in customUI:
<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>

Open in new window


To pre-answer the following remark:
I don't look to copy the default Protect Sheet ribbon command, as that one would ask for a password when (in-/re-)protecting and in my code (doProtectSheet) I have that part pre-programmed, as well as asking the password when it's a foreign file.

So: how do I get the Protect State back from a newly selected sheet or workbook and copy it to the Pressed State of the toggle button?

Any help much appreciated, GrtZ BartH
Bart HoekselBI/VBA DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Not tested, but if you use the UserInterfaceOnly argument you can probably do what you want. For example:
Sheets("Sheet3").Protect UserInterfaceOnly:=True

Open in new window


See this Microsoft article for more protection options:
Bart HoekselBI/VBA DeveloperAuthor Commented:
@Martin Liss
I am afraid this doesn't cover the issue.
Bart HoekselBI/VBA DeveloperAuthor Commented:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bart HoekselBI/VBA DeveloperAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Customized Ribbon Buttons

From novice to tech pro — start learning today.