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!
on September 1st, 2010 at 11:44 pm
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
on September 2nd, 2010 at 2:26 am
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…
on September 2nd, 2010 at 3:58 am
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
on September 2nd, 2010 at 6:47 am
@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.