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!
11 thoughts on “Enabling Outlining Commands on a Protected Worksheet”
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 🙂
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...
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
@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. 😉
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?
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.
I used the code you published and it worked fine but only issues i have is that everytime i close the file, i have to write the VB code again all over..
Is there a possible i can make it permanent?
Hi there,
You need to copy that code into the ThisWorkbook module, save the file as an xlsm file, and then it should run each time you open the workbook.
do you know if the Enable Outlining macro can be converted to script for Excel Online? I am struggling to find anything about this. I was able to use this article to write the macro and it works perfectly but now we are converting this file to Teams and excel online. Macros don't work there.... Thanks for your insight!
Hi Suzanne,
So the short answer is "I'm not sure". Here's the longer reasoning...
Office Scripts do not have an "event" which can be fired each time a workbook is opened. What that means is that - even if you could hook up an Office Script to do this - it will need to be run by every user before they can interact with the outlining tools. That means you'll either need to teach your users how to run your script, or assign it to a button that says "Click me before you do x..." Neither is particularly appealing to me.
I will reach out to some colleagues to see if the OM will allow us to do this at all, and will also provide some feedback to Microsoft that it is high time that this just gets fixed, and doesn't require code at all.
Hi Suzanne,
So unfortunately, the UserInterfaceOnly property has not been implemented for the JavaScript API as per https://docs.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.worksheetprotectionoptions?view=office-scripts. So at this time, there is not going to be a way to replicated this behaviour in Office Scripts. My hope is that they will add this property, but again, even when they do, you'll need to find a way to trigger it manually each time.