How to include password in Macro

[cmeylaq]

Hi guys,

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.

How do i go about this please?

Code: Select all

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

[Lupp]

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

Code: Select all

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

[cmeylaq]

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?

[Lupp]

Still dissuading.
For your additional considerations the attached example may be helpful.

strangeProtection.ods

(11.7 KiB) Downloaded 57 times

[cmeylaq]

Still dissuading.
For your additional considerations the attached example may be helpful.

Shift Reconciliation v4.ods

(34.11 KiB) Downloaded 53 times

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.

Clint

[Zizi64]

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.

[Zizi64]

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

Shift Reconciliation v4_Zizi64.ods

(35.59 KiB) Downloaded 43 times

[cmeylaq]

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 am not sure if im getting my point across

Appreciate any help I can get.

Regards
Clint

[Zizi64]

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!

Code: Select all

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.

[cmeylaq]

Unfortunately i am getting the attached error.

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

[RoryOF]

Look at
https://www.openoffice.org/api/docs/com ... iptor.html
for information on file handling with passwords.

[Zizi64]

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.

[Lupp]

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

(40.17 KiB) Downloaded 56 times

[cmeylaq]

Thank you so much Lupp

[cmeylaq]

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?

your help is truly appreciated.

Regards
Clint

[Zizi64]

How do i change the font of the LOCKED button to white please?

Please use an Object inspection tool.

Here is an example of the usage XrayTool:

XRAY_list.png (42.48 KiB) Viewed 1717 times

You can write the code line based on the information listed by the Xray:

Code: Select all

buttonModel.TextColor = RGB(255, 255, 255)