[libreoffice-users] Re: Enforcing Calc sheet protection

Am 17.04.2015 um 00:28 schrieb Andreas Säger:
> Am 16.04.2015 um 13:57 schrieb martin f krafft:
> > also sprach Andreas Säger <villeroy@t-online.de> [2015-04-16 13:05 +0200]:
> > > Sub protectSheets_onSave(ev)
> > > eSh = ev.Source.Sheets.createEnumeration()
> > > while eSh.hasMoreElements()
> > >     sh = eSh.nextElement()
> > >     sh.protect("")
> > > wend
> > > End Sub
> >
> > Also does not work. :(
>
>
> It DOES work
>
> > http://www.mediafire.com/view/y98whzbjn2t9q8c/protectSheetsOnSave.ods

Back to the drawing board.
The problem with the example macros that are shipped with the "Tools"
library of Open/LibreOffice is as follows:

The example macro Tools>Misc>protectSheets is declared as

Sub protectSheets(Optional oSheets)

When the optional argument is missing it unprotects all the sheets of
the current document. The optional argument can be a sheets collection.
When you call this macro by means of a push button or some document
event (Tools>Customize>Events) the macro gets some event struct argument
which is not an array of sheets which triggers the "wrong type of
object" errors.

-----------------------------------------------------------------
If I had a closer look at the example macro, my first answer would have
been this:

Sub protectAllSheets()
GlobalScope.BasicLibraries.loadLibrary("Tools")
Tools.Misc.protectSheets()
End Sub

This macro calls the example macro Tools.Misc.protectSheets without any
argument so it can do its job properly started from an active
spreadsheet window.

or more explicitly:

Sub protectAllSheets()
GlobalScope.BasicLibraries.loadLibrary("Tools")
Tools.Misc.protectSheets(ThisComponent.Sheets)
End Sub

passing the sheets collection of ThisComponent.
StarBasic's special variable ThisComponent is always the containing
document of a macro or the latest active document if the macro is not
contained in a document.

Second problem with Tools.Misc.protectSheets is the method how it grabs
the sheets of the active document when there is no sheet collection passed:

oDocSheets = StarDesktop.CurrentFrame.Controller.Model.Sheets

The model of the current frame's controller may return the StarBasic-IDE
while you are debugging your program. The Basic-IDE has no Sheets and
you get a "no such object or method" error.

I would prefer this one instead:
oDocSheets = ThisComponent.Sheets
which would fail if the latest active document is not a spreadsheet
document, not counting the IDE.

All this trouble with wrong arguments and types is impossible to resolve
for the common VBA/Excel programmer which is why I recommend to stay
away from all office macros unless you have a certain level of
proficiency, meaning that you are capable of systematically debuggig a
program and that you are very familiar with the application you are
hacking upon.
----------------------------------------------------------------

Instead of analysing the LibreOffice.Tools.Misc.unprotectSheets macro, I
simply wrote my own version of it presuming that it's called by a
document's script event from Tools>Customize, tab:Events.
The event passes an event struct having a Source object representing the
calling document in any case even when it is not the current
controller's model, not ThisComponent, invisible, not yet initialized or
otherwise inaccessible by the usual methods.

I am pretty sure that my code works under any circumstance and with all
the document events availlable (no, I did not test them all).

-------------------------------------------------------------------

And all these lengthy ramblings about the shortcomings and difficulties
with Libre/OpenOffice macro programming, why recorded macros don't work,
about the
http://en.wikipedia.org/wiki/Model%E2%80%93view%E2%80%93controller and
all the things that should be well known _before_  writing the first
macro are a massive waste of time.
How many sheets could have been protected manually while writing all
this techno rubbish?
Right-clickTabs>Select All Sheets.
Tools>Protect>Sheets
So simple.


-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted