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?

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

OkieViking wrote:

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

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

"Dave Peterson" wrote:

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

OkieViking wrote:

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

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

On Thu, 17 Nov 2005 14:31:10 -0800, "Joe M" <Joe
wrote:

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

"Dave Peterson" wrote:

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

OkieViking wrote:

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

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

"Gord Dibben" wrote:

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

On Thu, 17 Nov 2005 14:31:10 -0800, "Joe M" <Joe
wrote:

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

"Dave Peterson" wrote:

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

OkieViking wrote:

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 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.

Joe M wrote:

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

"Gord Dibben" wrote:

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

On Thu, 17 Nov 2005 14:31:10 -0800, "Joe M" <Joe
wrote:

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

"Dave Peterson" wrote:

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

OkieViking wrote:

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




--

Dave Peterson

Oops!

Missed that part.

See Dave P's post.

Just incorporate it into the macro.


Gord

On Thu, 17 Nov 2005 16:40:03 -0800, "Joe M"
wrote:

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

"Gord Dibben" wrote:

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

On Thu, 17 Nov 2005 14:31:10 -0800, "Joe M" <Joe
wrote:

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

"Dave Peterson" wrote:

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

OkieViking wrote:

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

Dave,

I am an amatuer code writer trying to figure out how to allow outlining on a
protected sheet. Where does the code below need to be inserted? Is this a
worksheet code? Will it automatically run when the workbook is open?

Thank you for the tip on David McRitchie's intro. I'm slowly going through
it.

Todd Evermon

"Dave Peterson" wrote:

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

OkieViking wrote:

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

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.





Todd Evermon wrote:

Dave,

I am an amatuer code writer trying to figure out how to allow outlining on a
protected sheet. Where does the code below need to be inserted? Is this a
worksheet code? Will it automatically run when the workbook is open?

Thank you for the tip on David McRitchie's intro. I'm slowly going through
it.

Todd Evermon

"Dave Peterson" wrote:

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

OkieViking wrote:

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


--

Dave Peterson

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