use Excel filter in protected report

[Former Member]

Hi,

we're using BPC 10 (SP 18) for MS. I have a protected worksheet with one report. There is a local member in that report, with the values 0 or 1. I want to enable my users to filter the rows with 1, and this should be done with a button triggering some vba. My code is

Columns("G:G").Select

ActiveSheet.Range("G:G").AutoFilter Field:=1, Criteria1:="1", Operator:=xlFilterValues

The problem is, that when I hit the button, I get the error message that I have to unprotect the sheet first. Alas, the checkbox allowing auto filter in Sheet Options -> Protection is checked, so this SHOULD be allowed, as far as I understood. When I enable the auto filter manually in Excel before protecting the sheet I can use the normal filter, but not the filter via my makro.

Does anybody have an idea how I can possibly handle this?

Thanks, Philipp

[Former Member · Accepted solution]

Hi guys,

so I guess there is no way to do what I want without unprotecting the worksheet... Well, guess I'll have to do it that way...

Thank you everybody!

[former_member152542 · Accepted solution]

Hi Philipp.

No. If you follow my instructions, there is no need to unprotect the worksheet before running the filter macro. After finishing the development of the template just use above macro to protect the worksheet.

In my case I use a combobox to filter some values:

Sub filter04()
Dim cb1_value As String
Set ws = Worksheets("P_Plan")

cb1_value = ws.ComboBox1.Value
Worksheets("P_Plan").Range("AA49:AA50000").Select
Selection.AutoFilter _
field:=4, _
Criteria1:=cb1_value, _
VisibleDropDown:=True

End Sub

Best regards,

Marco

[Former Member]

OK, now I got it... Sorry for being a bit slow, but it's Monday 🙂

[former_member152542]

Hi Philipp.

I have experienced the same problem. The final solution was to protect the epm sheet with the following macro (not manually, via the Ribbon Interface):

Public Sub Protect()

   Dim api As Object

   Set api = Application.COMAddIns("FPMXLClient.Connect").Object

   Dim SheetProtectionOptions As Long

   SheetProtectionOptions = FPMXLClient.ProtectSheet_ProtectContents + FPMXLClient.ProtectSheet_UserInterfaceOnly + FPMXLClient.ProtectSheet_AllowFiltering

   api.SetSheetOption Sheet1, 300, True, "YOUR_PASSWORD", SheetProtectionOptions

End Sub

Check the chapter 41.2.3.1 Sheet Options, of the EPMofc_10_user_en.pdf, for other sheet protection options.

Best regards,

Marco

[Former Member]

Hi,

I have never used a macro for filtering before, but the option in the sheet options only applies to the auto filter and not any sort of macro as far as I know.

You could change your macro to unprotect the sheet, filter the data and then re-add the protection.

Sheet1.Unprotect Password:="abc"

'Enter Code / Macro

Sheet1.Protect Password:="abc"

BR,
Arnold