Enforcing Calc sheet protection

users
#1

Dear all,

we've been pretty strict about locking our Calc sheets, turning cell
protection on by default and selectively unlocking those cells that
are meant to be edited.

Every now and then, some protected cells have to be changed.
Currently, the mode of operation is to unlock the sheet, edit, and
then lock the sheet again, before saving.

Unfortunately, that second-to-last step is often forgotten,
resulting in us committing a version of a document with unlocked
sheets.

Is there a better way to approach this? Is there maybe a trick how
to automatically protect all sheets on save, or to just temporarily
unlock them?

Thanks,

#2

Hi,

There is a macro under "LibreOffice Macros">Tools>Misc>ProtectSheets
which can be used together with Tools>Customize>Events>"Save Document"

A database with an input form would save you a lot of trouble, but this
would require that you forget about spreadsheets and accept a more
professional approach.

Greetings,
Andreas

#3

also sprach Andreas Säger <villeroy@t-online.de> [2015-04-15 17:40 +0200]:

There is a macro under "LibreOffice Macros">Tools>Misc>ProtectSheets
which can be used together with Tools>Customize>Events>"Save Document"

Oh, I didn't even know about all these macros. I'll investigate!
Thanks.

Unfortunately, ProtectSheets doesn't seem to work on an existing
document (while it works fine on a new document). If I assign it to
the "Save Document" event, then I get an error about a missing
"property or method" Count on the line

  For i = 0 To oDocSheets.Count-1

Do you have any idea what could be going on here? While I can read
this language, I have absolutely no idea about it. When I set
a break point on the macro, it's being ignored unless I run the
macro directly. And then, the error is a different one, it seems,
namely that

  StarDesktop.CurrentFrame.Controller.Model.Sheets

5 lines above is "out of scope" and (consequently) has no property
or method "Sheets".

A database with an input form would save you a lot of trouble, but
this would require that you forget about spreadsheets and accept
a more professional approach.

Oh, I am well aware that spreadsheets are hackery, but the use cases
I have (mainly budgeting in non-profits) require a high degree of
flexibility. I am not aware of any database that would be suitable
for these tasks, and creating one would require *way* more time than
scrambling along, especially since the structure of the budget (e.g.
for DebConf taking place in a different country every year) varies
considerably every year. Sure thing, I want to try to normalise this
a bit and then maybe the time for a database approach has come, but
for now, spreadsheets with locked cells are as good as it gets.

Thanks

#4

Those macros seem to be unmaintained since 15 years. This works with a
save event:

#5

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. :frowning:

At some points, I get an error when trying to run it. At other
times, the sheets are simply not protected after running it.

Also, it seems impossible to assign a macro stored in a file to an
event. I.e. when trying to assign a macro to "Save Document", the
macros stored in the .ods file are not available. :frowning:

I assume that this is because the macro is assigned locally, not "in
the document". In that case, I would have to make sure everyone
configures the macro in their libreoffice instances, which is
unrealistic (we can't even get everyone to install lint hooks for
Git etc.).

What I really just want is to tell the document that it should never
save the state of sheet protection and always open with all sheets
protected…

#6

The above macro works for me no matter where it is stored.

#7

Hi :slight_smile:
Hmm, does it depend on which OS and which version of LO?

It is good to know that it does work on at least 1 system, of course,
because that indicates it might be possible to make it work on others. I'm
not sure that aspect is clear from Andreas' post but he probably didn't
mean it to be as rude as it might have sounded. It is just something that
could potentially be helpful.

So, have you already told us which OS and version? Any chance of letting
us know, maybe again?
Regards from
Tom :slight_smile:

#8

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. :frowning:

It DOES work

#9

@tom

SHUT UP!!! If you don't have anything to tell.

#10

:frowning:

I understand both:

- Andreas, who is a high-level Libre/OpenOffice expert and really needs
exact and non-distracting communication

and

- Tom who is a genius in association and cross-connection but with a high
percentage of (sometimes incredibly wrong) guessing

Personally, I'm in favour of Andreas' way of communication as I also get
distracted too easy by Tom's frequent associations.

But as far as I perceive it, a bunch of subscribers do appreciate Tom's
friendly tone, even if the contents is sometimes not very helpful.

Does anybody know a good solution for this dilemma?

The perhaps clearest way would be to set up a list policy, which says that
only short answers are allowed, or that only solution suggestions are
welcome, which the writer has tested himself - or something similar. But
IMHO it's absolutely not easy/trivial to define exactly what is welcome and
what not.

A different idea could be to split the list into a "first" and "second"
level support, with diffrerent list policies (e.g. in list 2 only
to-the-point-communication, no distraction allowed).

Another idea could be to ask Andreas to try to ignore Tom's messages, and
ask Tom to try to restrain himself. Does this sound helpful?

Regards,
Nino

#11

<sigh> It's not going to work in the long run, Nino. There's probably
no solution other than letting Andreas blow his top now and then.

James

#12

I emphasise that there is no implied or intendedcriticism of Andreas in
my previous post.

The problem is that Tom is a very social person, and invaluable in not
frightening away insecure novices, but perhaps not the best poster when
the going gets technical.

James

#13

also sprach Tom Davies <tomcecf@gmail.com> [2015-04-16 21:06 +0200]:

I'm not sure that aspect is clear from Andreas' post but he
probably didn't mean it to be as rude as it might have sounded.
It is just something that could potentially be helpful.

Wait wait, I am a Debian developer for almost 20 years and a FLOSS
contributor for longer than that, which means two things: I know
perfectly well how to read messages on mailing lists, and I should
have done my homework better.

I appreciated Andreas' replies and didn't think they were rude.
I should have researched this better and provided more information.
Truth is that I was under time pressure and not particularly excited
to dive into MacroScript or whatever it's called.

So, no hard feelings. I hope I can catch up and deliver what
I should have sent in the first place.

#14

also sprach Nino Novak <nn.libo@kflog.org> [2015-04-17 10:21 +0200]:

But as far as I perceive it, a bunch of subscribers do appreciate
Tom's friendly tone, even if the contents is sometimes not very
helpful.

Yes, I would be surprised if there weren't a larger number of people
on this list who didn't appreciate his message.

Does anybody know a good solution for this dilemma?

Coexist and don't take things personal. And also try to stay away
from personal messages yourself. I.e. Tom could have sympathised
with me without guessing that I might have thought Andreas was being
rude.

If you're interested, there is
https://people.debian.org/~enrico/dcg/ and there was also recently
a fantastic article about how to choose your words on mailing lists,
but I am failing to find it. I will follow-up…

#15

Hi :slight_smile:
Thanks :slight_smile: Not everyone realises that and many of us on this mailing list
often do the same as Andreas did too :slight_smile:

A lot of people in IT have aspergers syndrome or similar things and we
don't always realise that people might misinterpret things we say. We
often just say exactly what we mean but then people add all sorts of
emotional stuff into it and sometimes even end-up thinking we've said the
reverse of what we really said.

Even non-Aspies in a lot of areas of IT get used to communicating in much
the same way = just direct and clear with no spin or anything.

I am kinda hyper-sensitive to things like that and try to help
Neurotypicals (NTs)
http://musingsofanaspie.com/2013/01/10/what-is-neurotypical/
to understand what was probably meant. I am probably so hyper-sensitive to
it because it is such a nightmare for me to try to get myself understood by
NTs. For many years i just thought certain sorts of people were being
deliberately obtuse but then i found a LOT of other people had the same
problem as me and that it's not something i can fix but is possible to
work-around the most frequent misunderstandings, sometimes.

Apparently Aspies are so common at Microsoft that their health-care now
includes testing employees children for it, if the employee wants. I
cynically suspect that it is to help spot likely programmers from a MUCH
earlier age but many aspies (and auties) are 'gifted' musicians or amazing
artists instead.

Regards from
Tom :slight_smile:

#16

Hi :slight_smile:
Btw to anyone who thinks i might be accusing anyone of being an aspie ...

It is not possible to "accuse" someone of being an aspie in much the same
way that it's not possible to "accuse" someone of being a super-hero.

Many of the aspie traits have an extremely positive and beneficial side.
It was/is the aspie/autie traits of people such as Bill Gates,
Michelangelo, Einstein, Mozart and many others that made/makes them
admirable.
Regards from
Tom :slight_smile:

#17

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.