software

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(quot;sheet1quot;)
.Protect Password:=quot;hiquot;, 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:
www.mvps.org/dmcritchie/excel/getstarted.htm

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

quot;Dave Petersonquot; wrote:

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

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:=quot;hiquot;, userinterfaceonly:=True
.EnableOutlining = True
.EnableAutoFilter = True
End With
application.goto thisworkbook.worksheets(1).range(quot;a1quot;), 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 lt;vbggt; when the code
ends.

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

--

Dave Peterson

I'm a little confused by what is the acutal code and what is part of the
comments? Say the 3 worksheets I want to protect are 01, 02, and 03...where
exactly would i code this in?quot;Dave Petersonquot; wrote:

gt; Looping is the way...
gt;
gt; Option Explicit
gt; Sub auto_open()
gt; dim wks as worksheet
gt; for each wks in thisworkbook.worksheets
gt; with wks
gt; .select 'see note below
gt; .Protect Password:=quot;hiquot;, userinterfaceonly:=True
gt; .EnableOutlining = True
gt; .EnableAutoFilter = True
gt; End With
gt; application.goto thisworkbook.worksheets(1).range(quot;a1quot;), scroll:=true
gt; End Sub
gt;
gt; Tom Ogilvy has reported that sometimes protecting sheets will work better if
gt; it's selected first.
gt;
gt; Change the application.goto line to where you want to goto lt;vbggt; when the code
gt; ends.
gt;
gt; Gizmo63 wrote:
gt; gt;
gt; gt; Wicked, thanks Dave, works a treat.
gt; gt;
gt; gt; This has been bugging me for a while now.
gt; gt; I understand the coding here and have set it up to work ok but is there a
gt; gt; way to apply to all the sheets in a work book?
gt; gt;
gt; gt; I'm happy to write a loop to cycle through the sheets and apply but I don't
gt; gt; know if there is a nice clean way of doing it.
gt; gt;
gt; gt; Cheers
gt; gt;
gt; gt; Giz
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; If you already have the outline/subtotals applied, you can protect the worksheet
gt; gt; gt; in code (auto_open/workbook_open??).
gt; gt; gt;
gt; gt; gt; Option Explicit
gt; gt; gt; Sub auto_open()
gt; gt; gt; With Worksheets(quot;sheet1quot;)
gt; gt; gt; .Protect Password:=quot;hiquot;, userinterfaceonly:=True
gt; gt; gt; .EnableOutlining = True
gt; gt; gt; .EnableAutoFilter = True
gt; gt; gt; End With
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; It needs to be reset each time you open the workbook. (excel doesn't remember
gt; gt; gt; it after closing the workbook.)
gt; gt; gt;
gt; gt; gt; If you're new to macros, you may want to read David McRitchie's intro at:
gt; gt; gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt; gt; gt;
gt; gt; gt; Gizmo63 wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; Hi guys,
gt; gt; gt; gt;
gt; gt; gt; gt; Is there any way of modifying the 'worksheet - protect' function, maybe
gt; gt; gt; gt; using startup VBA, that will let users open and close grouped columns and
gt; gt; gt; gt; rows but will still protect all the formulas and those rows that are truly
gt; gt; gt; gt; hidden.
gt; gt; gt; gt;
gt; gt; gt; gt; I really really need an answer to this, even if it's from an MVP, gold/
gt; gt; gt; gt; silver rated user who can uneqivocally say it cannot be done.
gt; gt; gt; gt; I know I can get round it by putting macros to show/hide the groups but i'd
gt; gt; gt; gt; rather not get this complicated.
gt; gt; gt; gt;
gt; gt; gt; gt; Many thanks - Giz
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;

Option Explicit
Sub auto_open() Dim wks As Worksheet For Each wks In ThisWorkbook.Worksheets With wksSelect Case LCase(.Name) Case Is = LCase(quot;01quot;), LCase(quot;02quot;), LCase(quot;03quot;) .Protect Password:=quot;hiquot;, userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True 'If .FilterMode Then ' .ShowAllData 'End IfEnd Select End With Next wks

End SubMichelle Thompson wrote:
gt;
gt; I'm a little confused by what is the acutal code and what is part of the
gt; comments? Say the 3 worksheets I want to protect are 01, 02, and 03...where
gt; exactly would i code this in?
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; Looping is the way...
gt; gt;
gt; gt; Option Explicit
gt; gt; Sub auto_open()
gt; gt; dim wks as worksheet
gt; gt; for each wks in thisworkbook.worksheets
gt; gt; with wks
gt; gt; .select 'see note below
gt; gt; .Protect Password:=quot;hiquot;, userinterfaceonly:=True
gt; gt; .EnableOutlining = True
gt; gt; .EnableAutoFilter = True
gt; gt; End With
gt; gt; application.goto thisworkbook.worksheets(1).range(quot;a1quot;), scroll:=true
gt; gt; End Sub
gt; gt;
gt; gt; Tom Ogilvy has reported that sometimes protecting sheets will work better if
gt; gt; it's selected first.
gt; gt;
gt; gt; Change the application.goto line to where you want to goto lt;vbggt; when the code
gt; gt; ends.
gt; gt;
gt; gt; Gizmo63 wrote:
gt; gt; gt;
gt; gt; gt; Wicked, thanks Dave, works a treat.
gt; gt; gt;
gt; gt; gt; This has been bugging me for a while now.
gt; gt; gt; I understand the coding here and have set it up to work ok but is there a
gt; gt; gt; way to apply to all the sheets in a work book?
gt; gt; gt;
gt; gt; gt; I'm happy to write a loop to cycle through the sheets and apply but I don't
gt; gt; gt; know if there is a nice clean way of doing it.
gt; gt; gt;
gt; gt; gt; Cheers
gt; gt; gt;
gt; gt; gt; Giz
gt; gt; gt;
gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; If you already have the outline/subtotals applied, you can protect the worksheet
gt; gt; gt; gt; in code (auto_open/workbook_open??).
gt; gt; gt; gt;
gt; gt; gt; gt; Option Explicit
gt; gt; gt; gt; Sub auto_open()
gt; gt; gt; gt; With Worksheets(quot;sheet1quot;)
gt; gt; gt; gt; .Protect Password:=quot;hiquot;, userinterfaceonly:=True
gt; gt; gt; gt; .EnableOutlining = True
gt; gt; gt; gt; .EnableAutoFilter = True
gt; gt; gt; gt; End With
gt; gt; gt; gt; End Sub
gt; gt; gt; gt;
gt; gt; gt; gt; It needs to be reset each time you open the workbook. (excel doesn't remember
gt; gt; gt; gt; it after closing the workbook.)
gt; gt; gt; gt;
gt; gt; gt; gt; If you're new to macros, you may want to read David McRitchie's intro at:
gt; gt; gt; gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt; gt; gt; gt;
gt; gt; gt; gt; Gizmo63 wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Hi guys,
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Is there any way of modifying the 'worksheet - protect' function, maybe
gt; gt; gt; gt; gt; using startup VBA, that will let users open and close grouped columns and
gt; gt; gt; gt; gt; rows but will still protect all the formulas and those rows that are truly
gt; gt; gt; gt; gt; hidden.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I really really need an answer to this, even if it's from an MVP, gold/
gt; gt; gt; gt; gt; silver rated user who can uneqivocally say it cannot be done.
gt; gt; gt; gt; gt; I know I can get round it by putting macros to show/hide the groups but i'd
gt; gt; gt; gt; gt; rather not get this complicated.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Many thanks - Giz
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt;
gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt;
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson

Option Explicit
Sub auto_open() Dim wks As Worksheet For Each wks In ThisWorkbook.Worksheets(Array _ (quot;01quot;, quot;02quot;, quot;03quot;)) With wks .Select 'see note below .Protect Password:=quot;justmequot;, userinterfaceonly:=True .EnableOutlining = True .EnableAutoFilter = True End With Next wks Application.Goto ThisWorkbook.Worksheets(1).Range(quot;a1quot;), Scroll:=True
End Sub

Code to be entered in a general/standard module.Gord Dibben MS Excel MVP

On Fri, 4 Dec 2009 13:08:01 -0800, Michelle Thompson
gt; wrote:

gt;I'm a little confused by what is the acutal code and what is part of the
gt;comments? Say the 3 worksheets I want to protect are 01, 02, and 03...where
gt;exactly would i code this in?
gt;
gt;
gt;quot;Dave Petersonquot; wrote:
gt;
gt;gt; Looping is the way...
gt;gt;
gt;gt; Option Explicit
gt;gt; Sub auto_open()
gt;gt; dim wks as worksheet
gt;gt; for each wks in thisworkbook.worksheets
gt;gt; with wks
gt;gt; .select 'see note below
gt;gt; .Protect Password:=quot;hiquot;, userinterfaceonly:=True
gt;gt; .EnableOutlining = True
gt;gt; .EnableAutoFilter = True
gt;gt; End With
gt;gt; application.goto thisworkbook.worksheets(1).range(quot;a1quot;), scroll:=true
gt;gt; End Sub
gt;gt;
gt;gt; Tom Ogilvy has reported that sometimes protecting sheets will work better if
gt;gt; it's selected first.
gt;gt;
gt;gt; Change the application.goto line to where you want to goto lt;vbggt; when the code
gt;gt; ends.
gt;gt;
gt;gt; Gizmo63 wrote:
gt;gt; gt;
gt;gt; gt; Wicked, thanks Dave, works a treat.
gt;gt; gt;
gt;gt; gt; This has been bugging me for a while now.
gt;gt; gt; I understand the coding here and have set it up to work ok but is there a
gt;gt; gt; way to apply to all the sheets in a work book?
gt;gt; gt;
gt;gt; gt; I'm happy to write a loop to cycle through the sheets and apply but I don't
gt;gt; gt; know if there is a nice clean way of doing it.
gt;gt; gt;
gt;gt; gt; Cheers
gt;gt; gt;
gt;gt; gt; Giz
gt;gt; gt;
gt;gt; gt; quot;Dave Petersonquot; wrote:
gt;gt; gt;
gt;gt; gt; gt; If you already have the outline/subtotals applied, you can protect the worksheet
gt;gt; gt; gt; in code (auto_open/workbook_open??).
gt;gt; gt; gt;
gt;gt; gt; gt; Option Explicit
gt;gt; gt; gt; Sub auto_open()
gt;gt; gt; gt; With Worksheets(quot;sheet1quot;)
gt;gt; gt; gt; .Protect Password:=quot;hiquot;, userinterfaceonly:=True
gt;gt; gt; gt; .EnableOutlining = True
gt;gt; gt; gt; .EnableAutoFilter = True
gt;gt; gt; gt; End With
gt;gt; gt; gt; End Sub
gt;gt; gt; gt;
gt;gt; gt; gt; It needs to be reset each time you open the workbook. (excel doesn't remember
gt;gt; gt; gt; it after closing the workbook.)
gt;gt; gt; gt;
gt;gt; gt; gt; If you're new to macros, you may want to read David McRitchie's intro at:
gt;gt; gt; gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt;gt; gt; gt;
gt;gt; gt; gt; Gizmo63 wrote:
gt;gt; gt; gt; gt;
gt;gt; gt; gt; gt; Hi guys,
gt;gt; gt; gt; gt;
gt;gt; gt; gt; gt; Is there any way of modifying the 'worksheet - protect' function, maybe
gt;gt; gt; gt; gt; using startup VBA, that will let users open and close grouped columns and
gt;gt; gt; gt; gt; rows but will still protect all the formulas and those rows that are truly
gt;gt; gt; gt; gt; hidden.
gt;gt; gt; gt; gt;
gt;gt; gt; gt; gt; I really really need an answer to this, even if it's from an MVP, gold/
gt;gt; gt; gt; gt; silver rated user who can uneqivocally say it cannot be done.
gt;gt; gt; gt; gt; I know I can get round it by putting macros to show/hide the groups but i'd
gt;gt; gt; gt; gt; rather not get this complicated.
gt;gt; gt; gt; gt;
gt;gt; gt; gt; gt; Many thanks - Giz
gt;gt; gt; gt;
gt;gt; gt; gt; --
gt;gt; gt; gt;
gt;gt; gt; gt; Dave Peterson
gt;gt; gt; gt;
gt;gt;
gt;gt; --
gt;gt;
gt;gt; Dave Peterson
gt;gt;