Grouping & collasping in a protected worksheet

  • Thread starter Thread starter Janice
  • Start date Start date
J

Janice

HELP!

I have created an Excel form and created groups that can
be expanded or collapsed by the user. However, once I
protect the sheet the user is denied access to the +/-
button that will perform the function. I have tried
adding a command button to use as a macro but the macro I
recorded won't perform the expand & collapse feature. Any
one have an alternative method?
 
for Excel 2002, you can use the code below. Most setting are
self-explanatory, the only thing is the last line - .EnableOutlining - this
allows to enable outline symbols to collapse/expand spreadsheet.

Best -
RADO

Public Sub Set_Protection()
' Protects worksheet and enables outline symbols

With Sheets("MySheet")
.Protect _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
UserInterfaceOnly:=True, _
AllowFormattingCells:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True, _
AllowInsertingColumns:=False, _
AllowInsertingRows:=False, _
AllowInsertingHyperlinks:=True, _
AllowDeletingColumns:=False, _
AllowDeletingRows:=False, _
AllowSorting:=True, _
AllowFiltering:=True, _
AllowUsingPivotTables:=True

.EnableSelection = xlNoRestrictions
.EnableOutlining = True
End with
End Sub
 
I am running Excell 2000. Will this still work?
-----Original Message-----
for Excel 2002, you can use the code below. Most setting are
self-explanatory, the only thing is the last line - .EnableOutlining - this
allows to enable outline symbols to collapse/expand spreadsheet.

Best -
RADO

Public Sub Set_Protection()
' Protects worksheet and enables outline symbols

With Sheets("MySheet")
.Protect _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
UserInterfaceOnly:=True, _
AllowFormattingCells:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True, _
AllowInsertingColumns:=False, _
AllowInsertingRows:=False, _
AllowInsertingHyperlinks:=True, _
AllowDeletingColumns:=False, _
AllowDeletingRows:=False, _
AllowSorting:=True, _
AllowFiltering:=True, _
AllowUsingPivotTables:=True

.EnableSelection = xlNoRestrictions
.EnableOutlining = True
End with
End Sub






.
Click to expand...
 
Janice,

It won't work in 2000. If you have a macro that works on an unprotected
sheet, you just need to add "UserInterfaceOnly" to your Protect statement
and the macro will work with a protected sheet:

ActiveSheet.Protect UserInterfaceOnly:=True 'specify the appropriate sheet

This Protect statement needs to run each time the workbook opens because
UserInterfaceOnly does not "stick" when the workbook is closed.

hth,

Doug
 
I think Doug meant to include the enableoutlining line:

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
End With
End Sub
 
Dave,

Actually, I was just plain wrong, but thanks for your diplomacy <g>. Thanks
to you experts, though, I do keep learning, even if at other's expense.

Thanks,

Doug
 
Back
Top