Start a new topic

EPM Add-In: Enable Group/Ungroup in Protected Sheet


When using the Excel feature 'Group/Ungroup' in a protected worksheet the groups cannot be opened and EPM functions do not work.


This is not caused by EPM Add-In but by a known limitation of Excel:


* Workaround 1

Copy the following VBA code into the module that appears:


Private Sub Workbook_Open()

    With Worksheets("Sheet1")

        .EnableOutlining = True

        .Protect UserInterfaceOnly:=True

    End With

End Sub

* Workaround 2

- Unprotect the report/input schedule in question by using EPM > Options > Sheet Options > Protection tab > select 'No Protection'
- Ungroup the affected rows by clicking the grouping (+) sign 
- Group it again by clicking on the grouping (-) sign
- Protect the report/input schedule using EPM > Options > Sheet Options > Protection tab > select 'Protect Workbook'  (enter password)
- Save this version (either locally or on server) and reopen it - from here on it will work as expected

Login or Signup to post a comment