Invoke Protection From Macro with 'Select Unprotected Cells'

[asymlabs]

I've done some searching for an answer to this, and noted another thread which indicated this was considered a 'bug' or feature request as of May 2014, but is there a way to invoke sheet protection from a macro that emulates the 'Select Unprotected Cells' option from Calc?

The best I could do for this was to modify a recorded macro as follows:

Code: Select all

REM SetProtectionOn()
Sub SetProtectionOn()
	
	'Purpose:  Wrapper for SetProtection.  Turn protection on.
	
	SetProtection( True )	
	
End Sub

REM SetProtectionOff()
Sub SetProtectionOff()

	'Purpose: Wrapper for SetProtection.  Turn protection off.
	
	SetProtection( False )

End Sub

REM SetProtection(a)
REM a is true or false, a boolean, optional
Sub SetProtection( Optional switch As Boolean )

	'Purpose:  To protect a spreadsheet.  This is developed from a macro
	'recording.  Invokes the standard protect/unprotect dialog.  Set pro-
	'tection on by default. 

	On Error GoTo ErrorHandler

	'Declare
	Dim _document As Object
	Dim _dispatcher As Object
	Dim _switch As Boolean
	Dim _args(0) As New com.sun.star.beans.PropertyValue
	
	'Initialize
	_document = ThisComponent.CurrentController.Frame
	_dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
	_switch = True 'On by default.
	If Not isMissing( switch ) Then
		_switch = switch
	End If
	
	_args(0).Name = "Protect"
	_args(0).Value = _switch 'True or False 
	
	_dispatcher.executeDispatch( _document,".uno:Protect","",0,_args() )
	
	Exit Sub
	
	ErrorHandler:
	
	'This traps errors that might occur when, for example, protection is already
	'on when SetProtection(True) is invoked.  So we simply stop execution to avoid
	'risk of undefined behaviour.
	
	Stop
	
End Sub

Of course this works but suffers from the requirement that the user has to deal with a dialog where choices must be made, so the user will need to know which box(es) to tick/untick ... not a major drawback but it could lead to some data loss.

So I was wondering if there are any secrets of the temple that might offer a way to achieve the same within a macro, without having to deal with any dialog interaction ...

[Villeroy]

Why don't you write macros rather than recording GUI dispatches?

sh = ThisComponent.CurrentController.getActiveSheet()
sh.protect(passwd)
sh.unprotect(passwd)

[asymlabs]

Hi Villeroy -

I did indeed write the macro first (concept from viewtopic.php?f=9&t=34393#p158410), but the resulting cell protection did not behave the same as when it is done via the calc dialog - it seems that it is not possible to specify the equivalent of the 'Select unprotected cells' option with just a macro, only via the uno api. The above code is the only way that I could do it. I'm hoping that someone here may be able to provide another way to do this with just a macro.

By the way, congratulations on Germany's World Cup victory. It was well deserved.

[B Marcelly]

The options in "Protect Sheet" window to allow Select locked cells and/or Select unlocked cells are badly implemented, see Bug 123703.
The API was not updated to reflect these options, and they are not described in help F1.

[asymlabs]

Thank you Marcelly. It's in inconvenience using the dispatcher, but not a disaster. Hopefully in future, when time and developer resources permit, these options can be implemented within the API.