Filtering lists while sheet is protected.

[dlangham]

:idea:

I am working in Excel 97 and have found that I cannot filter lists while the worksheet is protected. Is there any way to get around this limitation?

Thanks in advance for your help. :idea:

David

 

[Jotoya]

I don't think there is but someone might know different. If you are filtering via a macro, I'm afraid you will have to add a couple of lines of code to unprotect your sheet > filter > protect it again. Hope this helps.

 

[Tom Urtis]

FYI, That capability is available in xl2k2 but not 97 for AutoFilters. Otherwise as Jotoya said you'd need to sandwich your filter code between unprotect and protect lines.

 

[Juan Pablo González]

Actually, is quite easy to get it working in 97. Put this in the Workbook module, and change the sheet name and the password to suit yours:

<font face=Courier New>
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()
    <SPAN style="color:#00007F">With</SPAN> Sheets("Hoja1")
        .Protect "password", , , , <SPAN style="color:#00007F">True</SPAN>
        .EnableAutoFilter = <SPAN style="color:#00007F">True</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

it assumes that the AutoFilter is already turned on.

 

[Richie(UK)]

Hi David,

There is a way around this. See the link below:

http://www.ozgrid.com/forum/viewthread.php?tid=4350

HTH

 

[dlangham]

OK,

I have recorded a macro that will unprotect before filtering and protect after, however, I need greater control so that the user can only work with the filter fields.
So after unprotecting the worksheet, if I have a list with 4 columns; Region, Territory, Salesman and Amount I need a macro that will enble the user to select the column/cell to filter (Region, Territory, Salesman)and enable the action, then re-protect the WS.

 

[Juan Pablo González]

OK,

I have recorded a macro that will unprotect before filtering and protect after, however, I need greater control so that the user can only work with the filter fields.
So after unprotecting the worksheet, if I have a list with 4 columns; Region, Territory, Salesman and Amount I need a macro that will enble the user to select the column/cell to filter (Region, Territory, Salesman)and enable the action, then re-protect the WS.

David, try using the method I posted above (Which is the same as Richie linked to...)

 

[Tom Urtis]

Thanks for that reminder JPG !

 

[Juan Pablo González]

FWIW, that method also works for enabling Outlining, which is STILL not available in XP (Mhm...have to check 2003 to see if they have included it in that one...)

 

[Tom Urtis]

My gf has 2003 Beta on her computer...I'll check tonite.