Mofiying the protect option.

[Gizmo63]

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]

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]

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]

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.

 

[Michelle Thompson]

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?

 

[Dave Peterson]

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

End Sub

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?

 

[Gord Dibben]

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

Code to be entered in a general/standard module.


Gord Dibben MS Excel MVP

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?