I have a financial model that I set up using a grouping in some key places so that I could collapse sections of the model when I didn’t want to look at them. As I was handing off the model to someone else to work with, I wanted to protect the worksheets, but unfortunately there is no setting in the user interface to allow for expanding/collapsing the outlining tools when the sheet is protected. In fact, trying to do so gives you the following message:
I found this a little frustrating, but gave up on it. I expanded the model completely, protected the sheets and let the users have at ‘er.
Tonight at VBAExpress.com though, I was posting on a thread where the user had included the following in their code:
Sh.EnableOutlining = True
Wow! So obviously there IS a way to enable the outlining tools when the worksheet is protected, right? I ran the macro I had modified for the user and sure enough it worked. Cool!
So then I opened up a copy of my model and:
- Ran the following code: Activesheet.EnableOutlining = True
- Protected the worksheet
I didn’t work. What the hell?
After a little sleuthing I found out what the issue was. In order for the EnableOutlining to take effect, you must run the code that protects your worksheet with the userinterfaceonly:=true argument.
The unfortunate part of this is that userinterfaceonly:=true doesn’t stick between sessions. So that nice macro free workbook is now going to have to be saved into an xlsm format with the following code in it:
Private Sub Workbook_Open()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
.EnableOutlining = True
Application.ScreenUpdating = True
That shouldn’t be necessary in my opinion, but whatever. A macro laden file is a small price to pay for the functionality. Man I love VBA!