Enabling Outlining Commands on a Protected Worksheet

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

With ws

.Protect userinterfaceonly:=True

.EnableOutlining = True

End With

Next ws

Application.ScreenUpdating = True

End Sub

 

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!

6 thoughts on “Enabling Outlining Commands on a Protected Worksheet

  1. It's true, when you think you know ALL the ways to slice a particular piece of VBA cake a newbie comes along and says "...errr, wouldn't it be easier if you did it that way?"

    After a quick slap of the forehead the VBA adrenaline is off and running second guessing all those other issues you put to bed as having no other possibilities 🙂

  2. GREAT article.

    "Man I love VBA!" Even after all that?? LOL

    Ok, I agree, VBA is awesome... But the Excel object model could clearly be improved here in the two ways that you hit on: (1) the UserInterfaceOnly setting should persist with the workbook when the workbook is saved, and (2) all protection-related settings should be set within the Worksheet.Protect method itself, not in some separate field.

    A couple of versions ago (was it 2002? 2003?) Microsoft dramatically expanded the parameters within the Worksheet.Protect method to include most of these fields. It looks like they missed one though, I guess. Persisting the UserInterfaceOnly setting would be even more important imo.

    Awesome article...

  3. I mentioned this on the Data pigs blog ....The UserInterface = True does not help if you want to refresh a query / table on a protected worksheet

  4. @Sam... that sucks. Sounds like something got missed there...

    @Mike... of course I still love VBA. Without it I'd only be able to do a small fraction of what I do with this program. I totally agree on the object model point though. I was thinking the same thing when I found this... why isn't this a part of the protection method? I'd also love to see the userinterfaceonly property persist but, since it's existed in this form since... 97?... I kind of doubt we'll see a change there. 🙁

    @Simon... I'd never claim to know ALL the pieces of any code, but I hear you. I'd given up on this one, but I'm sure the neighbour saw the lightbulb go on when I saw that line. I do always tell people when I'm teaching classes that I expect to learn something from them, no matter how new they are. 😉

  5. Thanks for all your help. Yet I am puzzled by something: it seems these macros do not allow me to save the file protected WITH A PASSWORD and have the desired effect (use of filters and outlining).
    I cannot possibly put the password in the Workbook_Open() macro explicitly!
    Any thought on that?

  6. Hey Jerome,

    Yes, sorry, but the only way to deal with this issue is to un-protect and re-protect the worksheets on open. In order to do that, the password needs to be provided. If you store it in VBA, it's going to be in clear text. You could try and come up with some kind of salt algorithm and store it externally, but with the logic there, someone would be able to reverse engineer it. It's a known issue that I suspect will never be fixed.

Leave a Reply

Your email address will not be published. Required fields are marked *