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

Debaser · #2 03-20-2018, 02:54 AM

Why are you replicating a built-in control instead of just taking it over?

BartH · #3 03-20-2018, 04:24 AM

Quote:

Originally Posted by Debaser

Why are you replicating a built-in control instead of just taking it over?

Hi Debaser,

Because the Protect Sheet control would require entering the password when (re-)protecting the sheet and in my code (doProtectSheet) the password is pre-programmed (when it is a foreign file with an unknown password, the password will be requested and stored).

Grtz Brt

Debaser · #4 03-20-2018, 09:56 AM

I mean why don't you just hook into the built in button to run your code rather than its default?

BartH · #5 03-20-2018, 12:59 PM

Quote:

Originally Posted by Debaser

I mean why don't you just hook into the built in button to run your code rather than its default?

Ah, you mean repurposing the Protect Sheet button with onAction my own code... That is an interesting thought. I'll try that!

BartH · #6 03-22-2018, 03:22 AM

Hi Debaser,

I tried your suggestion and I am half-way now: I added the SheetProtect ribbon command to my tab and it is responding to the protected state of the selected sheet.

For some reason when I reassign the onAction of the protectSheet button to the procedure that I use for my button, I get an error message:

Code in the customUI:

When I repurpose ProtectSheet with the procedures cbDoProtectSheet or doProtectSheet I get another message (with or without the name of the addin in the onAction):

I must be missing something... Suggestions?

The code for (cbD/d)oProtectSheet is:

Code:

Sub cbDoProtectSheet(control As IRibbonControl)
    doProtectSheet
End Sub
Sub doProtectSheet()

' NAME    : modProcessAndRenameFiles - doProtectSheet
' AUTHOR  : Bart Hoeksel, Nedcom IT
' PURPOSE : doProtectSheet
Dim strMsg As String
Dim xWs As Worksheet
Set xWs = Application.ActiveWorkbook.ActiveSheet

If strTmpPass = "" Then strTmpPass = strPss
On Error GoTo fout
Application.DisplayAlerts = False
If ActiveSheet.ProtectContents Then
    xWs.Unprotect strTmpPass
    strMsg = "Sheet protection is " & UCase(ActiveSheet.ProtectContents)
Else
    xWs.Protect Password:=strTmpPass, Userinterfaceonly:=True, DrawingObjects:=True, Scenarios:=True, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True, Contents:=True
    xWs.EnableOutlining = True
    strMsg = "Sheet protection is " & UCase(ActiveSheet.ProtectContents)
End If
Application.DisplayAlerts = True

Debug.Print strMsg
Application.StatusBar = strMsg
Application.OnTime Now + TimeValue("00:00:06"), "clearStatus"
Set xWs = Nothing
Exit Sub

fout:
If Err.Number = 1004 Then
    strTmpPass = InputBox("What is the protection Password for this sheet?", "Get Password")
    If strTmpPass = "" Then
        Exit Sub
    Else
        Resume
    End If
Else
    MsgBox Err.Description, vbCritical, Err.Number
    Err.Clear
End If
Application.StatusBar = False
Set xWs = Nothing
End Sub

Grtz Bart

Debaser · #7 03-22-2018, 08:10 AM

If you're repurposing a command, the callback procedure declaration has to be slightly different, so that you can specify whether to allow the default action to run too:

Code:

Sub cbDoProtectSheetRepurpose(control As IRibbonControl, ByRef cancelDefault)
cancelDefault = True
doProtectSheet
End Sub

BartH · #8 03-22-2018, 09:12 AM

Quote:

Originally Posted by Debaser

If you're repurposing a command, the callback procedure declaration has to be slightly different, so that you can specify whether to allow the default action to run too:

Code:

Sub cbDoProtectSheetRepurpose(control As IRibbonControl, ByRef cancelDefault)
cancelDefault = True
doProtectSheet
End Sub

ThnX Debaser, I got it!

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