how can I allow users to open groups in a protected worksheet?

[Guest]

I am trying to protect a worksheet which has a number of groups. When I
protect the worksheet in order to lock certain cells the users are unable to
open groups. Is there a way of achieving this?

Many thanks

 

[raypayette]

You could use Information Rights Management by clicking File,
Permission, Restrict Permission as...
This requires a server that Microsoft provides for the time being.

 

[Guest]

Hi Victoria,
They won't be able to use groups when using regular protection. There is a
way around it by using some VBA that is executed when the workbook is opened.

In the VBA environment you need to select the Excel Object called 'This
Workbook' and then enter code like this:

Private Sub Workbook_Open()

Worksheets("Total").Select
ActiveSheet.Unprotect Password:="[your password - omit if no PW]"
With Worksheets(ActiveSheet.Name)
.Protect Password:="[your pword or null]", userinterfaceonly:=True
.EnableOutlining = True
End With

Worksheets("First").Select
ActiveSheet.Next.Select
Do Until ActiveSheet.Name = "Last"
ActiveSheet.Unprotect Password:="[your password - omit if no PW]"
With Worksheets(ActiveSheet.Name)
.Protect Password:="[your pword or null]", userinterfaceonly:=True
.EnableOutlining = True
End With
ActiveSheet.Next.Select
Loop

Worksheets("Functions").Select
End Sub


Essentially this code is executed when the workbook is opened then does 3
things
1. Selects the 'Total' sheet, unprotects it and then reprotects but enables
'outlining'
2. It then cycles through all sheets between 'First' and 'Last' repeating pt
1 on each sheet.
3. It returns the user to a predefined start sheet in the workbook:
'Functions'

This only takes a couple of seconds in a workbook with 15 sheets so is not
overly intrusive.

HTH Giz