How to use Outline on a protected sheet

  • Thread starter Thread starter 0Kim Jerkhagen
  • Start date Start date
0

0Kim Jerkhagen

I have late in the process of making a template for job estimation, found
out that if I protect a worksheet, the outline bar to the side of the sheet
will also become protected.
I never even considered the possibility of this when I decided to use
outline since it does not change any data in the sheet. I use it to enable
the user to only expand the part of the sheet he/she is working on.
Now I either have to "get back to the drawing board" or leave the worksheet
unprotected.
Neither is very appealing so the question I have is if anyone know a way
around this.
I really only need to protect the sheet from accidental deletion of
formulas, it is not needed for secrecy.

Help!

Kim
 
Read this from Dave Peterson
************************

If you protect the worksheet in code, you can do more things:

Add this to a general module:

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

(you could use the workbook_open event under ThisWorkbook, too.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
If you protect the worksheet programmatically, you can enable outlining,
and you will be able to use the groups that you have created.

The following code goes in the ThisWorkbook module:

Private Sub Workbook_Open()
With Worksheets("Sheet1")
.EnableOutlining = True
.Protect Password:="password", _
Contents:=True, UserInterfaceOnly:=True
End With

End Sub

To paste the code into the ThisWorkbook module:

Right-click on the Excel icon, to the left of the File menu
Choose View Code
Paste the code where the cursor is flashing.
 
Thanks a million Debra.
You made my day.

Kim

Debra Dalgleish said:
If you protect the worksheet programmatically, you can enable outlining,
and you will be able to use the groups that you have created.

The following code goes in the ThisWorkbook module:

Private Sub Workbook_Open()
With Worksheets("Sheet1")
.EnableOutlining = True
.Protect Password:="password", _
Contents:=True, UserInterfaceOnly:=True
End With

End Sub

To paste the code into the ThisWorkbook module:

Right-click on the Excel icon, to the left of the File menu
Choose View Code
Paste the code where the cursor is flashing.
Click to expand...
 
Back
Top