Cell Protection - Subtotal


A

amepluie

Hi All,

I have a worksheet with Subtotal function for several Groups.
The idea is the user can enter the data then the subtotal function can
calculate the SUM/COUNT or whatever.
So I want to lock the lock the SUBTOTAL row (so the user come mess it
up), which is easy enough to do.

the problem comes....

I have a large groups of subtotals so naturally I want to show the
outline at high level and user can expand to which data group they
want to enter in. But seems any lock within the outline will block
that?

Is there a way around it?

Would really appreciate someone's help!!!

Thanks,
Kerry
 
Ad

Advertisements

D

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.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 

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

Top