[Dropped] Lock sheets but allow filters by macro

[Nikos]

High guys, long time no see.

I am trying to write a macro that locks selected sheets of a spreadsheet, while allowing using filters in Libreoffice calc 25.2.
The version seems to be important here, as the macro I previously used does no longer work. (It does lock the sheets, but it does not allow using filters and pivot tables.


The no longer working macro is the following:

Code: Select all

Sub lock_sheets
Dim Doc As Variant
Doc = ThisComponent
Dim Sheet As Object
Dim sheet_to_lock as String

Dim sheets_to_lock(12) as string
sheets_to_lock(0) = "Sheet1"
sheets_to_lock(1) = "Sheet2"
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Protect"
args1(0).Value = true

For i = 0 to 12
	If len(sheets_to_lock(i))>1 Then 
	Sheet = Doc.Sheets.getByName(sheets_to_lock(i))
	dispatcher.executeDispatch("IKnowWhatIDo",document, ".uno:Protect", "", 0, args1())
REM 	Sheet.protect("IKnowWhatIDo")
	End If
Next i

End Sub

Please do not provide untested ChatGPT answers...I have been there.

[Lupp]

What you show is a mutilated macro which was originally a recorded macro with a few additional written lines.
As posted it does neither create the the needed dispatchhelper nor the dispatchprovider.
The call to the (undefined) dispatchhelper has a funny first argument and can't work anyway.

[Lupp]

Code: Select all

Sub protectSheetsByNames(Optional pSheetNamesPws)
If IsMissing(pSheetNamesPws) Then pSheetNamesPws = Array(Array("Sheet1", ""), Array("Sheet2", ""))
REM The elements of pSheetNamesPws are arrays (sequences) which have in turn
REM two elements each: with index(0 the sheetname, with index 1 the password to use.
REM The defaults are given in row 4.
 Dim theSheets As Object, s_Index As Long, s_sheetNamePw, s_sheet As Object
theSheets = ThisComponent.Sheets

For s_Index = 0 To Ubound(pSheetNamesPws)
 s_sheetNamePw = pSheetNamesPws(s_Index)
 s_sheet = theSheets.getByName(s_sheetNamePw(0))
 s_sheet.protect(s_sheetNamePw(1))
Next s_Index

End Sub

Like the originally posted code this does not worry about filters.
I don't know for sure, but would assume that the options enabled last time when a sheet was interactively protected, are applied again.

[JeJe]

New sheet protection options related to Pivot Tables, Pivot Charts and AutoFilters were added. tdf#160404 tdf#160535 tdf#160536

https://wiki.documentfoundation.org/ReleaseNotes/25.2

"Use Pivot Table and Pivot Chart" is a new tickbox option in the Tools/Protect Sheet dialog

[JeJe]

Unfortunately the macro recorder doesn't record anything different if that box is ticked though.

[JeJe]

There are no options except the password in the API version of calling Protect... the result may be a setting in the sheet properties, but I can't find anything with MRI (Edit: someone else may fare better). Perhaps the new setting can be accessed as a property via the dispatch, but you would need to find or guess the name, if the option exists.

There's a very complicated way of "Controlling a dialog I did not create." found in Useful Macro Information For OpenOffice.org By Andrew Pitonyak which might enable you to run a macro which opens the dialog and sets the tickboxes.

Failing that your only option may be to revert to any earlier version.

[Nikos]

I don't know for sure, but would assume that the options enabled last time when a sheet was interactively protected, are applied again.

This indeed seems to be the case. Is this "last time" something stored in the sheet (then it is actually acceptable) or in the user profile. In the latter case I definitely will need to dig into one of @JeJe options.