Enabling a list autofilter in a protected worksheet - Excel 2003

[Spottydog]

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

 

[Jim Rech]

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

--
Jim
|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]

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

 

[Jim Rech]

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