Macro to protect spreadsheet without UI popup

[jfg.sosten]

Hi.

I am trying to protect a Calc sheet while specifying some attributes, like allowing to select non-protected cell and disallowing the selection of cells that are protected.

These attributes are available via the user interface by right-clicking the sheet name and selecting those options. However, I would like to trigger those options in a macro without having the UI popup.

How could this be done?

I have attached a spreadsheet with an embedded macro, but it does not work.

Test_Protect_Sheet.ods (13.9 KB)

Any suggestions?

Thanks

JF

[Villeroy]

See container “Application Macros”, library “Tools”, module “Misc”, routine “ProtectSheets”

[jfg.sosten]

Thanks for your reply, Villeroy.

I was able to load the “Misc” module from the “Tools” library using the following command: GlobalScope.BasicLibraries.LoadLibrary(“Tools”).

I was also able to test it using the following code:
oData = GetRegistryKeyContent(“org.openoffice.UserProfile/Data”, True)
MsgBox "oData.givenname: " & oData.givenname & " and oData.sn: " & oData.sn
Msgbox "Product name: " & GetProductname

However, calling:
Sub ProtectSheets(Optional oSheets as Object)
Dim i as Integer
Dim oDocSheets as Object
If IsMissing(oSheets) Then
oDocSheets = StarDesktop.CurrentFrame.Controller.Model.Sheets
Else
Set oDocSheets = oSheets
End If

For i = 0 To oDocSheets.Count-1
	oDocSheets(i).Protect("")
Next i

End Sub

return the message “Basic runtime error: Property or method not found: Sheets.” in reference to the statement: “StarDesktop.CurrentFrame.Controller.Model.Sheets”

Even if I could fix this Basic runtime error, I still don’t know how to pass the specific arguments to set the way I would like the sheet to be protected (for example, “Select unprotected cells” only). Are those arguments documented somewhere? How do I pass it to the “ProtectSheets” Subroutine?

Thanks again for your help on this.