Invoke Protection From Macro with 'Select Unprotected Cells'

Creating a macro - Writing a Script - Using the API

Invoke Protection From Macro with 'Select Unprotected Cells'

Postby asymlabs » Tue Jul 15, 2014 11:07 pm

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   Expand viewCollapse view
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 ...
Last edited by asymlabs on Wed Jul 16, 2014 12:03 am, edited 1 time in total.
LibreOffice 4.2.5.2 ArchLinux/Fedora Systems
asymlabs
 
Posts: 28
Joined: Tue Jul 08, 2014 11:26 am

Re: Invoke Protection From Macro with 'Select Unprotected Ce

Postby Villeroy » Tue Jul 15, 2014 11:45 pm

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

sh = ThisComponent.CurrentController.getActiveSheet()
sh.protect(passwd)
sh.unprotect(passwd)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28551
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Invoke Protection From Macro with 'Select Unprotected Ce

Postby asymlabs » Wed Jul 16, 2014 12:08 am

Hi Villeroy -

I did indeed write the macro first (concept from https://forum.openoffice.org/en/forum/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.
LibreOffice 4.2.5.2 ArchLinux/Fedora Systems
asymlabs
 
Posts: 28
Joined: Tue Jul 08, 2014 11:26 am

Re: Invoke Protection From Macro with 'Select Unprotected Ce

Postby B Marcelly » Tue Jul 22, 2014 2:23 pm

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

OpenOffice.org 1.1.5 / Apache OpenOffice 4.1.1 / LibreOffice 5.0.5
MS-Windows 7 Home SP1
B Marcelly
Volunteer
 
Posts: 1160
Joined: Mon Oct 08, 2007 1:26 am
Location: France, Paris area

Re: Invoke Protection From Macro with 'Select Unprotected Ce

Postby asymlabs » Tue Jul 22, 2014 5:52 pm

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.
LibreOffice 4.2.5.2 ArchLinux/Fedora Systems
asymlabs
 
Posts: 28
Joined: Tue Jul 08, 2014 11:26 am


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 5 guests