Worksheet Protection and Data Grouping/Outlining

[Jim Skrydlak]

I have been developing a personal financial planning model
that can show either summary data or detailed data,
depending on the level of grouping chosen. I would like
to protect each of the worksheets to keep users from
inadvertently modifying cells. When I protect a
worksheet, however, it is no longer possible to change the
level of grouping (clicking on the + or - to the left of
the worksheet proper just gives an error message). Is
there a way around this? VBA is an acceptable solution; I
write a lot of it.

Thanks!

 

[Dave Peterson]

If you already have the outline 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
End With
End Sub

It needs to be reset each time you open the workbook. (excel doesn't remember
it after closing the workbook.)


there's a similar option for allowing filters (see .enableautofilter in help).

 

[Guest]

Hi,
Where to put this code when my financial macro is cretatig abt n excel
workbook.
how to assign to each work book.

thanks

 

[Dave Peterson]

I'm not sure how your financial macro works, but his code goes into a General
module.

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

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side. Paste the code
there.

===
This has to be put into each workbook that you create.

Either you could create a template with the code already existing in the module
and use that in your financial macro, or you could use some of the techiques at
Chip Pearson's site to add that code.

http://www.cpearson.com/excel/vbe.htm