Expand/collapse function with protected sheets

[Guest]

I would like to know if it is possible to use the expand/collapse function on
worksheets that have ben protected. More specifically, I need to be able to
lock or protect individual cells without loosing the possibility of
expanding/collapsing rows.

 

[Ragdyer]

AFAIK,
You can't use those features on protected sheets.

 

[Debra Dalgleish]

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

 

[Guest]

Thanks for your reply. I am finally getting back to this and am unable to
make it work. I have protected certain cells within the sheet, protected the
sheet and cut and pasted the code as you suggested. I eliminated the
"quotes" and substituted the appropriate sheet title and password. I saved
the workbook, then closed and reopened the file.

I continue to get the same error message that I cannot use that function on
a protected sheet. Any sugggestions?

Also, if I include a list of sheets separated by commas will this apply to
each sheet?

Obviously, this is new to me, and I appreaciate your help. Thanks.

 

[Dave Peterson]

And you put that workbook_open macro behind the ThisWorkbook module--not behind
a worksheet, not in a General Module????

 

[Guest]

Sorry, I am new to code. I opened the "this module" window and pasted in the
text as shown. It didn't work. I saved and closed the workbook and it still
didn't work. I still get "You can't do that on a protected sheet." Let me
ask some "stupid" questions.

Does anything go in the ( ) after workbook_open ?
Is there a wildcard indication for all sheets in the workbook that goes in
the Parens ( ) after Worksheets? How to list sheets individually, with
commas and spaces? What about sheet titles that have spaces in the name of
the sheet?
Protect the workbook or sheet before or after pasting in the code ?
If I protect the whole workbook am I protecting the structure or Windows?

Thanks for any help you can give me. Inwood

 

[Dave Peterson]

Nothing goes in the ()'s for this procedure:


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

But it only protects a worksheet named Sheet1. If you have multiple sheets,
then every other sheet wasn't affected.

If you can list the worksheet names:

Private Sub Workbook_Open()

Dim mySheetNames As Variant
Dim iCtr As Long

'change to match your workbook
mySheetNames = Array("sheet1", "sheet2", "sheet3")

For iCtr = LBound(mySheetNames) To UBound(mySheetNames)
With Worksheets(mySheetNames(iCtr))
.Select
.EnableOutlining = True
.Protect Password:="password", _
Contents:=True, UserInterfaceOnly:=True
End With
Next iCtr
End Sub

This will open the workbook with Sheet3 selected. Put the worksheet name that
you want to see selected when it's done as the last name.

 

[Guest]

Fantastic!

I had just about given up on getting this to work. One last question. If I
protect the workbook, are all sheets automatically protected? If not what's
the difference?

Thanks, Dave

 

[Dave Peterson]

No.

Workbook protection (via tools|protection|protect workbook), you'll see two
options.

Protect Structure and protect windows.

If you protect the structure, the user can't delete/add/rename sheets.

If you protect windows, they can't resize/move/hide/close the windows.

===
You can find out what a lot of things do by using the context sensitive help
button.

Try tools|protect|protect workbook
just to see that dialog.

At the top right corner, there's a ? icon. Click on that and your cursor
changes to a pointer with an arrow. If you click on one of those options,
you'll see a nice tooltip.

There's lots of dialogs with with this ? icon.

You can even add it to your favorite toolbar in excel (if it's not already
there).

Tools|customize|commands tab
Window and help category
Drag the "what's this" icon to your favorite toolbar.

(you can also get to this context sensitive help by clicking on the item and
hitting shift-F1.)