Skip to Content

Worksheet generation removes sheet protection

Jun 20, 2017 at 03:06 AM


avatar image
Former Member

Hi All,

BPC 10.1, BW 7.5 SP2, AFO V2.3

I have a template which is password protected. I have named ranges with 3 ___ which are used in formatting sheet. I also have some macros - after_refresh to allow ungrouping of columns and keep sheet protected, etc.

When I am generating multiple sheets for a dimension using worksheet generation option, there are a few issues -

1. It removes the password protection from the sheets, however edit report button is greyed out.

2. Formatting goes for a toss as all cells are yellow while I had few as grey and few as yellow based on name ranges - not sure if it is because of named ranges.

I have the inherit sheet options activated in template and Apply dynamic formatting sheet is activated in sheet options, so same formatting sheet should apply all across.

Is this known behavior (specially on the password protection)? Any solutions if someone has come across this earlier?



10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Vadim Kalinin Jun 20, 2017 at 07:51 AM

Try AO 2.4!

You can also write yourself worksheet generation procedure in VBA - just a simple copy sheet with page axis change...

Show 7 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Vadim,

I wrote a VBA to copy sheet in VBA - this code also removes sheet protection and formatting is not applied at all.

So, both the issues are still there with custom VBA. Password protection can be done using the same VBA, but I am concerned about the formatting. I have tried applying formatting through sheet options and using inherit sheet option in report editor, and directly applying through report with inherit sheet options turned off.

I don't think AO version matters now that the issue is still there with custom vba.

Any other thoughts please?

Former Member

Further, edit report button is greyed out even though there is no protection on the sheet. So, to work on it, I first have to put some password, lock it and then unlock again and only then edit report button becomes available.


Just tested on standalone EPM Add-In SP28 Patch 3

No issues using standard worksheet generation!

Try simple template, may be you VBA macros somehow affect the generation...

Former Member

Thanks Vadim. I too tested with a basic template and the results are -

1. Sheet protection is removed and edit report button greyed out. So, this is consistent with the issue I had with my template.

2. Formatting remains in tact so I suspect on my template, it might be because of the named ranges. need to do few more tests.

But given these limitations, I think I will have to park this option.


My test was on SP28:

1. Simple single sheet report with formatting sheet (color formatting).

2. Enabled protection for report sheet.

3. Perform worksheet generation for the dimension in page axis (3 members).

4. 3 sheets are generated with protection and formatting OK!

Former Member

Thanks for your tests, Vadim. I have the issues as mentioned earlier.


Looks like the issue is specific for AO....