Mofiying the protect option.

Hi guys,

Is there any way of modifying the 'worksheet - protect' function, maybe
using startup VBA, that will let users open and close grouped columns and
rows but will still protect all the formulas and those rows that are truly
hidden.

I really really need an answer to this, even if it's from an MVP, gold/
silver rated user who can uneqivocally say it cannot be done.
I know I can get round it by putting macros to show/hide the groups but i'd
rather not get this complicated.

Many thanks - Giz

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

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

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

Gizmo63 wrote:
>
> Hi guys,
>
> Is there any way of modifying the 'worksheet - protect' function, maybe
> using startup VBA, that will let users open and close grouped columns and
> rows but will still protect all the formulas and those rows that are truly
> hidden.
>
> I really really need an answer to this, even if it's from an MVP, gold/
> silver rated user who can uneqivocally say it cannot be done.
> I know I can get round it by putting macros to show/hide the groups but i'd
> rather not get this complicated.
>
> Many thanks - Giz

--

Dave Peterson

Wicked, thanks Dave, works a treat.

This has been bugging me for a while now.
I understand the coding here and have set it up to work ok but is there a
way to apply to all the sheets in a work book?

I'm happy to write a loop to cycle through the sheets and apply but I don't
know if there is a nice clean way of doing it.

Cheers

Giz

"Dave Peterson" wrote:

> If you already have the outline/subtotals 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
> End With
> End Sub
>
> It needs to be reset each time you open the workbook. (excel doesn't remember
> it after closing the workbook.)
>
> If you're new to macros, you may want to read David McRitchie's intro at:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> Gizmo63 wrote:
> >
> > Hi guys,
> >
> > Is there any way of modifying the 'worksheet - protect' function, maybe
> > using startup VBA, that will let users open and close grouped columns and
> > rows but will still protect all the formulas and those rows that are truly
> > hidden.
> >
> > I really really need an answer to this, even if it's from an MVP, gold/
> > silver rated user who can uneqivocally say it cannot be done.
> > I know I can get round it by putting macros to show/hide the groups but i'd
> > rather not get this complicated.
> >
> > Many thanks - Giz
>
> --
>
> Dave Peterson
>

Looping is the way...

Option Explicit
Sub auto_open()
dim wks as worksheet
for each wks in thisworkbook.worksheets
with wks
.select 'see note below
.Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
.EnableAutoFilter = True
End With
application.goto thisworkbook.worksheets(1).range("a1"), scroll:=true
End Sub

Tom Ogilvy has reported that sometimes protecting sheets will work better if
it's selected first.

Change the application.goto line to where you want to goto <vbg> when the code
ends.

Gizmo63 wrote:
>
> Wicked, thanks Dave, works a treat.
>
> This has been bugging me for a while now.
> I understand the coding here and have set it up to work ok but is there a
> way to apply to all the sheets in a work book?
>
> I'm happy to write a loop to cycle through the sheets and apply but I don't
> know if there is a nice clean way of doing it.
>
> Cheers
>
> Giz
>
> "Dave Peterson" wrote:
>
> > If you already have the outline/subtotals 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
> > End With
> > End Sub
> >
> > It needs to be reset each time you open the workbook. (excel doesn't remember
> > it after closing the workbook.)
> >
> > If you're new to macros, you may want to read David McRitchie's intro at:
> > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> >
> > Gizmo63 wrote:
> > >
> > > Hi guys,
> > >
> > > Is there any way of modifying the 'worksheet - protect' function, maybe
> > > using startup VBA, that will let users open and close grouped columns and
> > > rows but will still protect all the formulas and those rows that are truly
> > > hidden.
> > >
> > > I really really need an answer to this, even if it's from an MVP, gold/
> > > silver rated user who can uneqivocally say it cannot be done.
> > > I know I can get round it by putting macros to show/hide the groups but i'd
> > > rather not get this complicated.
> > >
> > > Many thanks - Giz
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson