I created the below macro using the macro recorder to protect the sheet with a password.
When i run the macro i am asked for the password.
I would like to include the password in the macro itself. Lets say the password is LOCKME.
REM ***** BASIC *****
sub CLOSESHIFT
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
rem ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:FormatCellDialog", "", 0, Array())
rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Protect"
args2(0).Value = true
dispatcher.executeDispatch(document, ".uno:Protect", "", 0, args2())
end sub
Where will the macro be stored? It will contain the plain password.
If you just want to protect sheets against accidental changes you may openly use the sheetname or something to associate with it in a verty simple way. Otherwise I would suggest you don't tamper with sheet protection. (Differently from saving documents by encoding, it's not safe against attacks anyway.)
Sub protectAllSheetsByTheirNameAsPW()
sheetDoc = ThisComponent
For Each singleSheet In sheetDoc.Sheets
singleSheet.protect(singleSheet.Name)
Next singleSheet
End Sub
Sub unprotectAllSheetsByTheirNameAsPW()
sheetDoc = ThisComponent
For Each singleSheet In sheetDoc.Sheets
success = tryUnprotect(singleSheet, singleSheet.Name)
If NOT success
REM Nothing yet implemented here.
EndIf
Next singleSheet
End Sub
Function tryUnprotect(pSheet, pPW) As Boolean
REM Indirection to avoid breaks by errors.
tryUnprotect = False
On Local Error Goto fail
pSheet.unprotect(pPW)
tryUnprotect = True
fail:
End Sub
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Thanks for replying. Basically i have a file with a number of sheets. Each sheet will have a button. Once the users completes the input in the sheet he will press this button and the sheet is protected with the password LOCKME. I dont want the user ti know this password. I know that it can be accessed through the macro but i can live with that.
How can i add the password in the macro so it does not ask the user to insert the password?
Hi Lupp, Many thanks for coming back.
With your code I managed to create a simple macro to protect the sheet.
However my issue is that since the sheet is already password protected to allow the user to insert figures in the highlighted cells only, when the user presses the CLOSE SHIFT button, these cells still remain unprotected.
Therefore I need to amend the macro to protect these cells before it password protects the sheet.
The idea is that when the user presses CLOSE SHIFT no changes are allowed in the sheet.
I would also like to change the CLOSE SHIFT to LOCKED with red background when the CLOSE SHIFT is pressed (similar to the example u sent me). Tried doing it myself but didnt manage:(
PASSWORD is LOCK
I am attaching the sheet. Would really appreciate your help.
However my issue is that since the sheet is already password protected to allow the user to insert figures in the highlighted cells only, when the user presses the CLOSE SHIFT button, these cells still remain unprotected.
You must set the Cell protection property of the light blue cells to protected. It will be activated only after the Sheet will be protected by the macro.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
My Tips:
- Use Cell styles to formatting Cells instead of the manual (direct) formating method. It more easier to modify the properties of cells what use same Style: You need modify the property od the Style only. I just created one cell style named "InputCells". (I have set the Data validity feature again: it is not a part of the Cell Style.) The Cell protection properts is to "Unprotected" in my sample file. Try to modify the Cell Style in the Unprotected state of all sheets.
- Use Object Event to launch the macro (the Form Control elements has such property). Then you will able to determine which object launched the macro routine: then you can format THAT object. I have used Button in the modified sample file, and the macro sets the Background color of the Button. (You must change it back to light blue manually after unprotect the Sheet - or you can write an another macro for a new task: "Unprotect sheet".)
- Use one of the excellent Object inspection Tools: XrayTool or MRI. Then you will able to list and examine the existing properties and methods of the programming objects.
.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
However my issue is that since the sheet is already password protected to allow the user to insert figures in the highlighted cells only, when the user presses the CLOSE SHIFT button, these cells still remain unprotected.
You must set the Cell protection property of the light blue cells to protected. It will be activated only after the Sheet will be protected by the macro.
Thank you Zizi.
This sheet will be used by a cashier and therefore it is already password protected to not allow the cashier to amend the sheet except to input figures in the highlighted cells. Therefore I can not protect the highlighted cells because he will not be able to use them.
Therefore the macro, before fully password protecting the sheet, has to activate the protection of the highlighted cells.
I just Xrayed the properties of the Cell Style named "InputCells", now I can modified the Protection property of the cell style
(I never used these "commands" (objects and properties) of the API before...)
API: Application Programming Interface
I strongly suggest you: Use an Object Inspection Tool!
sub lockME(oEvent)
'Xray oEvent
oModel = oEvent.Source.Model
'Xray oModel
oDoc = ThisComponent
oStyleFamilies = oDoc.getStyleFamilies()
oCellStyles = oStyleFamilies.getByName("CellStyles")
oCellStyle = oCellStyles.getByName("InputCells") 'This is a Custom cell style for the input cells.
'Xray oCellStyle
oProtection = oCellStyle.CellProtection
'Xray oProtection
oProtection.isLocked = true
oCellStyle.setPropertyValue ("CellProtection", oProtection)
oSheet = ThisComponent.CurrentController.ActiveSheet
oSheet.protect("LOCK")
oModel.BackgroundColor = RGB(255, 0, 0)
end sub
Tha macro was created and tested in my LibreOffice 6.4.7.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
I am trying to follow the logic of the macro (Im new to this). What i cant understand is how will the macro know which cells are the "input cells" since i do not see any reference to these cells in your macro. Sorry if its a dumb question but im very new to this and im trying to follow the logic..
Attachments
macro error
Screenshot 2023-04-20 114047.jpg (84.09 KiB) Viewed 2080 times
What i cant understand is how will the macro know which cells are the "input cells" since i do not see any reference to these cells in your macro.
My macro will not modify the cell properties directly (one-by-one), my macro will modify the Cell Style applied to the input cells.
Unfortunately i am getting the attached error.
Sorry, the macro can not modify a Cell Style what is assigned to a protected cell located on a protected Sheet. It must unprotect all of the sheets temporarly by the macro.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
The design of the sheets has serious flaws telling us that the author most likely never before has designed sheets for actual use.
In the given case in specific maintenance and enhancement are aggravated uselessly. Take the intermittent empty rows as an example.
Then, a shape can get a link to a macro, but has otherwise none of the properties qualifying button controls for their purposes. Mainly: It can't be identified as the object having caused the call by the handling macro, and it can't be "switched off" (.Enabled = False) without removing the link.
Spreadsheets are made for calculations, and your sheets expect numeric entries. Therefore I would assume there will later be also some evaluation by formulas. The intermittent rows might drive you nuts then.
Lupp wrote: ↑Thu Apr 20, 2023 3:15 pm
The design of the sheets has serious flaws telling us that the author most likely never before has designed sheets for actual use.
In the given case in specific maintenance and enhancement are aggravated uselessly. Take the intermittent empty rows as an example.
Then, a shape can get a link to a macro, but has otherwise none of the properties qualifying button controls for their purposes. Mainly: It can't be identified as the object having caused the call by the handling macro, and it can't be "switched off" (.Enabled = False) without removing the link.
Spreadsheets are made for calculations, and your sheets expect numeric entries. Therefore I would assume there will later be also some evaluation by formulas. The intermittent rows might drive you nuts then.
See attached
ShiftExperiments5.ods
Hi Lupp,
I got to grips with your code. THANK YOU!!
I would like to update the macro to NOT ALLOW the cashier to close the shift if Cell H24 is not equal to 0. Can he be prompted with a pop up "PLEASE BALANCE THE SHIFT FIRST"?
One other thing. How do i change the font of the LOCKED button to white please?
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.