How execute command in vba for analysis microsoft excel to protect, unprotect BPC 10.1 embedded

[Former Member]

Hi Expert,

I need to execute a command in vba for analysis for office to protect and unprotect a book. Does anybody know if it exists? (BPC embedded)

ActiveSheet.Protect -> but for Analysis protect.

Thank you!

[former_member283567]

Hello,
I have a similar requirement using AFO with BW Integrated Planning input sheets with buttons to trigger planning sequences.
When activating the AFO worksheet protect, some vba commands I use in the Callback_AfterRedisplay module are not allowed any more, such as range.merge and cell.value to add some text in a cell, even with all 13 allow checkboxes ticked in the AfO worksheet lock settings.

The following 3 steps worked for me:

1) Activate AFO Worksheet protection and allow all necessary actions (I ticked all 13 Allow checkboxes)

2) Use the following VBA code in my Callback_AfterRedisplay sub:
- Parameter UserInterfaceOnly allows VBA commands

Worksheets("MySheet").Protect Password:="MyPw", _
    DrawingObjects:=False, _
    Contents:=True, _
    Scenarios:=False, _
    UserInterfaceOnly:=True, _
    AllowFormattingCells:=True, _
    AllowFormattingColumns:=True, _
    AllowFormattingRows:=True, _
    AllowInsertingColumns:=True, _
    AllowInsertingRows:=True, _
    AllowInsertingHyperlinks:=True, _
    AllowDeletingColumns:=True, _
    AllowDeletingRows:=True, _
    AllowSorting:=True, _
    AllowFiltering:=True, _
    AllowUsingPivotTables:=True<br>

3) I had to adjust the standard SAP style SAPEditableDataCell by un-ticking the "Lock" checkbox in the style.
With a de-activated Lock checkbox in the cell property the cell is not locked with the worksheet protection active.
- In the old BEx this was the standard setting. I never had to change it.

In this way I was able to enter data in input-ready cells and use my buttons to trigger planning sequences, while the non-input-ready cells are protected, giving the standard Excel warning message when trying to enter some value.

Kind regards.

[gerd_schoeffl]

Yes - one has to use the same password. Interestingly I used a workbook for testing that was protected by AO (with password), then I used Excel functionality/VBA to un-protect (entering the pwd) and protected the sheet again WITHOUT password. Still the AO features did work fine. Nevertheles I would always use the pwd.

Best regards, Gerd

[former_member186338]

"protected the sheet again WITHOUT password. Still the AO features did work fine."

If the sheet is protected without password then unprotect function will work even with some password 🙂

But after AO will protect the sheet back after finishing update the old password will be restored.

I have spend some time in the past to find the place where the password is stored for EPM Add-In (Not for AO), please read my blog: https://blogs.sap.com/2016/12/01/how-to-get-epm-protection-password/

The same can be done for AO...

[gerd_schoeffl]

Well, this is true. But if you just use the normal workbook/sheet protection then AO cannot perform all actions changing locked cells anymore (e.g. hide totals). If you first lock the workbook with the AO locking described above you can un-lock and re-lock the workbook by VBA. I just tested it and in my case all AO features work. Thus the AO locking is a prerequisite. Best regards, Gerd

[former_member186338]

But without changing the password! When the workbook is locked by AO, the password is saved in the file and used to temporary unlock file for AO operations.

[gerd_schoeffl]

Hi Ricardo,

In Analysis for Office 2.4 this feature is available in AO. Click 'File' in the menu of an AO workbook and go to 'Analysis'. You will find 'Protect Workbook' as last entry in the list of offered features.

Best regards, Gerd

[former_member186338]

The question was about VBA API, not about menu...

[former_member186338]

As far as I know there is no special protection API for Analysis tab...

[Former Member]

Hi,

Analysis tab.

Thank you!

[former_member186338]

Please explain what are you using? Analysis tab or EPM tab?