I have a list set up within a worksheet and I need to protect the worksheet
and still be able to filter the list.

If I select Protect Sheet and check Enable Autofilter then it works but this
option prevents me being able to collapse the rows using the grouping.

I therefore tried a macro I found here so that the grouping works but this
stops my list filter working. The macro I'm using is:

Sub workbook_open()
With Worksheets("My Sheet Name")
.Protect , userinterfaceonly:=True
.EnableOutlining = True
.EnableAutoFilter = True
End With

Any suggestions on a command I can include in the macro so the list filter
and grouping both work in the protected sheet?

Thanks

When I run your code in XL2003 both autofilter and outlining work. I can
send my simple workbook if you like.

--
Jim
"Spottydog" wrote in message
...
|I have a list set up within a worksheet and I need to protect the worksheet
| and still be able to filter the list.
|
| If I select Protect Sheet and check Enable Autofilter then it works but
this
| option prevents me being able to collapse the rows using the grouping.
|
| I therefore tried a macro I found here so that the grouping works but this
| stops my list filter working. The macro I'm using is:
|
| Sub workbook_open()
| With Worksheets("My Sheet Name")
| .Protect , userinterfaceonly:=True
| .EnableOutlining = True
| .EnableAutoFilter = True
| End With
|
| Any suggestions on a command I can include in the macro so the list
filter
| and grouping both work in the protected sheet?
|
| Thanks

This worked ok for me--allowing filtering of lists and filtering using
data|filter|autofilter.

Option Explicit
Sub workbook_open()
With Worksheets("My Sheet Name")
.Protect userinterfaceonly:=True, AllowFiltering:=True
.EnableOutlining = True
End With
End Sub

Spottydog wrote:

I have a list set up within a worksheet and I need to protect the worksheet
and still be able to filter the list.

If I select Protect Sheet and check Enable Autofilter then it works but this
option prevents me being able to collapse the rows using the grouping.

I therefore tried a macro I found here so that the grouping works but this
stops my list filter working. The macro I'm using is:

Sub workbook_open()
With Worksheets("My Sheet Name")
.Protect , userinterfaceonly:=True
.EnableOutlining = True
.EnableAutoFilter = True
End With

Any suggestions on a command I can include in the macro so the list filter
and grouping both work in the protected sheet?

Thanks

--

Dave Peterson

Yes, I missed that you're doing this with an official 'list'. I tried this
code and it seemed to allow filtering with a list:

..Protect userinterfaceonly:=True, AllowFiltering:=True

--
Jim
"Spottydog" wrote in message
...
|I think I possibly didn't explain myself correctly.
|
| I have set up a list by selecting a number of cells with the top cell
being
| the header (say E4 to E38) Then I select Data--List--Create List and my
| selection shows in the pop up plus I select "My list has headers". On
| clicking OK Excel creates an autofilter dropdown on the header cell which
| covers the cells in my list only.
|
| This is a separate filter to the Data--Filter--Autofilter (although
Excel
| also marks the auto filter as switched on with it) and I can also set up
an
| autofilter at the same time in the spreadsheet (Excel swaps between them
| enabling the list filter when you click on a cell in the list and the main
| autofilter if you click anywhere else in the worksheet).
|
| Its the list filter that does not work when the macro is run but works
when
| I select protect from the menubar
|
| "Jim Rech" wrote:
|
| When I run your code in XL2003 both autofilter and outlining work. I
can
| send my simple workbook if you like.
|
| --
| Jim
| "Spottydog" wrote in message
| ...
| |I have a list set up within a worksheet and I need to protect the
worksheet
| | and still be able to filter the list.
| |
| | If I select Protect Sheet and check Enable Autofilter then it works
but
| this
| | option prevents me being able to collapse the rows using the grouping.
| |
| | I therefore tried a macro I found here so that the grouping works but
this
| | stops my list filter working. The macro I'm using is:
| |
| | Sub workbook_open()
| | With Worksheets("My Sheet Name")
| | .Protect , userinterfaceonly:=True
| | .EnableOutlining = True
| | .EnableAutoFilter = True
| | End With
| |
| | Any suggestions on a command I can include in the macro so the list
| filter
| | and grouping both work in the protected sheet?
| |
| | Thanks
|
|
|