Page 1 of 1
[Dropped] Lock sheets but allow filters by macro
Posted: Mon Mar 10, 2025 3:47 pm
by 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.
Re: Locking Sheets while allowing using filters by macro
Posted: Mon Mar 10, 2025 10:02 pm
by 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.
Re: Locking Sheets while allowing using filters by macro
Posted: Mon Mar 10, 2025 11:02 pm
by 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.
Re: Locking Sheets while allowing using filters by macro
Posted: Tue Mar 11, 2025 12:47 am
by 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
Re: Locking Sheets while allowing using filters by macro
Posted: Tue Mar 11, 2025 1:00 am
by JeJe
Unfortunately the macro recorder doesn't record anything different if that box is ticked though.
Re: Locking Sheets while allowing using filters by macro
Posted: Tue Mar 11, 2025 1:40 am
by 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.
Re: Locking Sheets while allowing using filters by macro
Posted: Tue Mar 11, 2025 10:23 am
by 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.