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



"Janice" wrote in message
...
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?

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



"Janice" wrote in
message
...
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?


.

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

wrote in message
...
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



"Janice" wrote in
message
...
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?


.

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



Doug wrote:

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

wrote in message
...
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



"Janice" wrote in
message
...
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?


.


--

Dave Peterson

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

"Dave Peterson" wrote in message
...
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



Doug wrote:

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

wrote in message
...
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



"Janice" wrote in
message
...
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?


.


--

Dave Peterson