Outlining on a protected spreadsheet



I have created an extensive spreadsheet for a construction developer
including budget and payment columns for 3 years, each year containing 4 Qtrs
and each Qtr containing 3 months. There is data to be inputted for each
months payments, etc and Qtr and YTD totals are totaled in separate columns.

I have set up my spreadsheet with outlining for the Qtrs and YTD so they
expand and collapse for easy viewing. It works great. However when I lock the
cells with formulas, etc and then protect the worksheets I can no longer
expand the outline columns.
Help!! This is critical!!/p>



Dave Peterson

If you already have the outline/subtotals/autofilter applied, you can protect
the worksheet in code (auto_open/workbook_open??).

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
'.EnableAutoFilter = True
'If .FilterMode Then
' .ShowAllData
'End If
End With
End Sub

It needs to be reset each time you open the workbook. (Earlier versions of
excel don't remember it after closing the workbook. IIRC, xl2002+ will remember
the allow autofilter setting under tools|Protection|protect sheet, but that
won't help when you're filtering via code.)

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question