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?

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?

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?

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?

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?