Enable groups on a protected sheet
 

I have a workbook which has grouping on certain sheets, i want the workbook
to protect itself everytime a user closes it. The problem i'm having is that
when the workbook is opened the sheets are protected but the user cannot
expand the groups. I have written the code below

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht as Workbooksheet
For Each sht In ActiveWorkbook.Worksheets
sht.Activate
With Worksheets(ActiveSheet)
.Protect Password:="pwd", Contents:=True, UserInterfaceOnly:=True
.EnableOutlining = True

End With
Next sht
End sub

I'm sure its something simple that i'm doing wrong, can someone please help?

Enable groups on a protected sheet
 

Somthing like the below...(I havent tested this)

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In Me.Worksheets
ws.Protect Password:="pwd", Contents:=True, UserInterfaceOnly:=True
ws.EnableOutlining = True
Next
Me.Save
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"hervinder" wrote:

I have a workbook which has grouping on certain sheets, i want the workbook
to protect itself everytime a user closes it. The problem i'm having is that
when the workbook is opened the sheets are protected but the user cannot
expand the groups. I have written the code below

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht as Workbooksheet
For Each sht In ActiveWorkbook.Worksheets
sht.Activate
With Worksheets(ActiveSheet)
.Protect Password:="pwd", Contents:=True, UserInterfaceOnly:=True
.EnableOutlining = True

End With
Next sht
End sub

I'm sure its something simple that i'm doing wrong, can someone please help?

Enable groups on a protected sheet
 

Hi Jacob

thanks for a quick reply

unfortunately i have the same problem, it protects each sheet but doesnt
allow the user to expand the groups



"Jacob Skaria" wrote:

Somthing like the below...(I havent tested this)

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In Me.Worksheets
ws.Protect Password:="pwd", Contents:=True, UserInterfaceOnly:=True
ws.EnableOutlining = True
Next
Me.Save
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"hervinder" wrote:

I have a workbook which has grouping on certain sheets, i want the workbook
to protect itself everytime a user closes it. The problem i'm having is that
when the workbook is opened the sheets are protected but the user cannot
expand the groups. I have written the code below

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht as Workbooksheet
For Each sht In ActiveWorkbook.Worksheets
sht.Activate
With Worksheets(ActiveSheet)
.Protect Password:="pwd", Contents:=True, UserInterfaceOnly:=True
.EnableOutlining = True

End With
Next sht
End sub

I'm sure its something simple that i'm doing wrong, can someone please help?

Enable groups on a protected sheet
 

Oops..I just worked on the code syntax issue...On the grouping, I dont think
you can do that with the sheet protected. We will wait for more responses
around this..

If this post helps click Yes
---------------
Jacob Skaria


"hervinder" wrote:

Hi Jacob

thanks for a quick reply

unfortunately i have the same problem, it protects each sheet but doesnt
allow the user to expand the groups



"Jacob Skaria" wrote:

Somthing like the below...(I havent tested this)

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In Me.Worksheets
ws.Protect Password:="pwd", Contents:=True, UserInterfaceOnly:=True
ws.EnableOutlining = True
Next
Me.Save
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"hervinder" wrote:

I have a workbook which has grouping on certain sheets, i want the workbook
to protect itself everytime a user closes it. The problem i'm having is that
when the workbook is opened the sheets are protected but the user cannot
expand the groups. I have written the code below

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht as Workbooksheet
For Each sht In ActiveWorkbook.Worksheets
sht.Activate
With Worksheets(ActiveSheet)
.Protect Password:="pwd", Contents:=True, UserInterfaceOnly:=True
.EnableOutlining = True

End With
Next sht
End sub

I'm sure its something simple that i'm doing wrong, can someone please help?

Enable groups on a protected sheet
 

Thanks for your help jacob

hopefully someone else will be able to help

"Jacob Skaria" wrote:

Oops..I just worked on the code syntax issue...On the grouping, I dont think
you can do that with the sheet protected. We will wait for more responses
around this..

If this post helps click Yes
---------------
Jacob Skaria


"hervinder" wrote:

Hi Jacob

thanks for a quick reply

unfortunately i have the same problem, it protects each sheet but doesnt
allow the user to expand the groups



"Jacob Skaria" wrote:

Somthing like the below...(I havent tested this)

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In Me.Worksheets
ws.Protect Password:="pwd", Contents:=True, UserInterfaceOnly:=True
ws.EnableOutlining = True
Next
Me.Save
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"hervinder" wrote:

I have a workbook which has grouping on certain sheets, i want the workbook
to protect itself everytime a user closes it. The problem i'm having is that
when the workbook is opened the sheets are protected but the user cannot
expand the groups. I have written the code below

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht as Workbooksheet
For Each sht In ActiveWorkbook.Worksheets
sht.Activate
With Worksheets(ActiveSheet)
.Protect Password:="pwd", Contents:=True, UserInterfaceOnly:=True
.EnableOutlining = True

End With
Next sht
End sub

I'm sure its something simple that i'm doing wrong, can someone please help?