Start a new topic

EPM Add-In: Availability of Expand & Collapse Buttons on Protected Worksheet

Symptom:


The expand / collapse options & buttons are available in the "Data Analysis" section of the EPM toolbar. In that section you can carry out the standard expand / collapse functions, or you can select alternative / custom expand options.

However, when sheet protection is enabled, these options are grayed out. Is there any way to enable these options for end users when sheet protection is enabled?

Cause:

This is an issue with Excel. It can be solved using VBA.


Resolution:

- right click on the worksheet tab
- select "View Code"
- expand the VBA project
- expand the Microsoft Excel object
- double click on the "This workbook"
- enter the below code:


Option Explicit

Sub Workbook_Open()
With Worksheets("Sheet1")
.Protect userinterfaceonly:=True
.EnableOutlining = True
End With
End Sub


- save it
- go to the Excel Options -> Trust Center -> Trust Center Settings -> Macro Settings

- check “Enable all macros” in order for the workbook macro to run successfully when the workbook is opened.


Login or Signup to post a comment