Formatting Protection

[cmeylaq]

Guys desperately need assistance.

Is there a way to FORBID users from selecting the protected cells in a sheet locked by a macro? (this functionality is available when protecting the sheet from Tools / Protect Document.

The problem is that users are copying the closing float in a locked sheet and pasting them in the opening float of an unlocked sheet. When the close shift button is pressed the sheet is locked but the pasted cells from the previous sheet can still be amended.

In the attached example i locked Day 1 Shift 1. All Good. I copied cells G9 and G10 of Day 1 Shift 1 to cells C9 and C10 in Day 1 Shift 2. When i pressed the close shift button in Day 1 Shift 2 cells C9 and C10 did not become protected.

I hope that I managed to explain my problem.

[Zizi64]

Google is your friend:
viewtopic.php?t=34393

See Villeroy's sample code.

API informations:
https://www.openoffice.org/api/docs/com ... ction.html
https://www.openoffice.org/api/docs/com ... Flags.html

I strongly suggest you to use an Object Inspection Tool for the programming.

(XrayTool, MRI)

[cmeylaq]

Thank you Zizi. Honestly before i posted the thread i googled and tried several suggestions but still didnt manage. I think i need to change the logic and do not work with styles. Perhaps have the close shift macro unprotect all cells and reprotect all cells in worksheet before locking. However i like the style formatting features.......

[Zizi64]

I think i need to change the logic and do not work with styles.

You can set the CELL PROTECTION property by styles/macro, but you must manage the SHEET PROTECTION too. These two things together will protect the sheets/cell as you desired.

[cmeylaq]

Not sure if my problem is clear. The sheet works well. When the close shift macro is run the sheet is all locked. The only problem is that the user can select cells from the locked sheet and paste it in a new sheet. When this is done the pasted cells in the new sheet lose their style and this is screwing up everything.

Is there a way the macro disallows the user from selecting the locked cells so he cabt copy and paste?

Your help ia much appreciated. I need to go live with this asap and would love to have it full proof.

[JeJe]

If you look at Useful Macro Information For OpenOffice.org By Andrew Pitonyak it gives an example of intercepting menu commands so you can prevent the copy - may not be stable. Another way might be to change the menu items and keyboard shortcuts for copy to point to a macro which sees if the unwanted cells are selected and nullifies the operation.

Also there is an example of using the selection change listener - so you could possibly change the selection if the unwanted cells were selected. Right click on a sheet tab and look at sheet events. Again - may or may not be problematic...

[cmeylaq]

If you look at Useful Macro Information For OpenOffice.org By Andrew Pitonyak it gives an example of intercepting menu commands so you can prevent the copy - may not be stable. Another way might be to change the menu items and keyboard shortcuts for copy to point to a macro which sees if the unwanted cells are selected and nullifies the operation.

Also there is an example of using the selection change listener - so you could possibly change the selection if the unwanted cells were selected. Right click on a sheet tab and look at sheet events. Again - may or may not be problematic...

Thank you Jeje for your assistance. I thought of changing the keyboard shortcut but I have to do it on all user PCs which is not practical given the no of users. Tried following the 'intercept menu command' and 'selection change listener' but it is way over my knowledge. I think my only option is to ask users to paste special values if they decide to copy and paste

[cmeylaq]

In excel i can remove cell selection with this code:

ActiveSheet.Protect "password"
ActiveSheet.EnableSelection = xlNoSelection

Is there a similar code in openoffice?

[JeJe]

Here's the API for all the sheet information. You can also explore with MRI. And put Option VBASupport 1 at the top of the module to see if any VBA code is supported.

https://www.openoffice.org/api/docs/com ... le-ix.html

https://api.libreoffice.org/docs/idl/re ... sheet.html

You could make it difficult to select cells by putting a supersized form controls label (transparent, with no text and position protected) over the sheet. That could be set to have a tooltip saying please not to copy and paste from the sheet.

You can run key and mouse handlers to stop user input - but those are a little complicated as well.

[cmeylaq]

Hi Jeje. Thanks for trying 2 help out. Unfortunately I am finding it very difficult to follow and understand the info you shared. Didnt think that this could be so complicated.

I am finding alot of resources in excel but very limited info on Open office.

[Villeroy]

If "finding code" is your one and only programming skill, you better stick with Excel.
Where do you find the equivalent of ActiveSheet.EnableSelection = xlNoSelection in Calc's user interface?

[JeJe]

The attached document has a keyhandler started by the document's view created event (Tools menu/Customize) which stops any keypresses working if the sheet named sheet1 has the focus.

Unless someone knows better - what you want can be done - but its not simple and it involves some coding and some risk of things going wrong running a handler: best avoided unless you really need it.

The form control suggestion (Form controls toolbar, add a label and size it to cover the sheet) would stop the user selecting with the mouse at least and is simple and probably risk free.

The links were for you to answer your own questions... you'll need to put some effort in with OOBasic... many hours - its often not as easy as VBA.

[cmeylaq]

Thank you Jeje. Will try this out and revert!

[cmeylaq]

I tried incorporating the code (which is exactly what i want when they press the close shift button) in my macro but i think i did an absolute mess Cant get the hang of it. Nevertheless I thank you for your time helping me out!! Must accept that this is out of my comfort zone