Sheet protection and "Group and Outline"

[Guest]

I have a worksheet with 3 big tables on it that looks good in Outline view
(Showing only the 3 total rows). The sheet is full of equations that needs
to be protected. If I protect the sheet, the user will not be able to
expand/collapse the Outline view. Is there a way to protect the sheet AND
allow the user to collapse/expand the Group and Outline view?

 

[Dave Peterson]

If you already have the outline 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
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

 

[Guest]

Dave, This works great. Thanks

Can you tell me how I allow the user to add and delete rows once protected?

I also want to project multiple worksheets. I added the following:

With Worksheets("Clarence")
.Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
.EnableAutoFilter = True
End With

With Worksheets("JoeM")
.Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
.EnableAutoFilter = True
End With

Is there a better way to do this for 7 sheets?

JoeM

 

[Gord Dibben]

Joe

Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
With Sheets(n)
.Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
.EnableAutoFilter = True
End With
Next n
Application.ScreenUpdating = True
End Sub


Gord Dibben Excel MVP

 

[Guest]

Thanks Gord, that works for protecting all sheets, but does not allow me to
insert or delete rows. Any suggestions?

 

[Dave Peterson]

If you have xl2002 or higher, you can protect the worksheet and allow the user
to insert & delete rows/columns.

If you don't have xl2002+, then maybe you could give them a macro that
unprotects the worksheet, inserts/deletes what you want and then reprotects the
worksheet.

ps. for xl2002+, you can record a macro when you set this protection and you'll
see the syntax:

I got:

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowInsertingColumns:=True, _
AllowInsertingRows:=True, AllowDeletingColumns:=True, _
AllowDeletingRows:=True

when I did it.

Thanks Gord, that works for protecting all sheets, but does not allow me to
insert or delete rows. Any suggestions?

 

[Gord Dibben]

Oops!

Missed that part.

See Dave P's post.

Just incorporate it into the macro.


Gord

Thanks Gord, that works for protecting all sheets, but does not allow me to
insert or delete rows. Any suggestions?

 

[Dave Peterson]

The name of this procedure is Auto_Open.

If you make sure that you put that procedure in a General module (not behind a
worksheet, not behind ThisWorkbook), then Auto_Open will run each time you open
the workbook--well, if your security settings allow macros to run.

 

[Guest]

I have the following code in my workbook. It allos the user to utilize the
outline and grouping, but it will not allow them to add and delete rows.

Private Sub Workbook_Open()
With Worksheets("vendorcommodities_detail")
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowInsertingColumns:=True, _
AllowInsertingRows:=True, AllowDeletingColumns:=True, _
AllowDeletingRows:=True
.Protect Password:="add", userinterfaceonly:=True
.EnableOutlining = True
End With
End Sub

Since I am new to VBA, I am having trouble locating why it wont work.

Thanks
DIane