Filtering lists while sheet is protected.

J

Jotoya

Active Member

Joined

May 25, 2003

Messages

366

• Jun 30, 2003

• #2

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.

 

Upvote 0

Tom Urtis

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,305

• Jun 30, 2003

• #3

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.

 

Upvote 0

J

Juan Pablo González

MrExcel MVP

Joined

Feb 8, 2002

Messages

11,959

• Jun 30, 2003

• #4

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.

 

Upvote 0

Richie(UK)

MrExcel MVP

Joined

May 17, 2002

Messages

3,329

• Jun 30, 2003

• #5

Hi David,

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

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

HTH

 

Upvote 0

D

dlangham

New Member

Joined

Feb 28, 2003

Messages

12

• Jun 30, 2003

• #6

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.

 

Upvote 0

J

Juan Pablo González

MrExcel MVP

Joined

Feb 8, 2002

Messages

11,959

• Jun 30, 2003

• #7

dlangham said:

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.

Click to expand...

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

 

Upvote 0

Tom Urtis

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,305

• Jun 30, 2003

• #8

Thanks for that reminder JPG !

 

Upvote 0

J

Juan Pablo González

MrExcel MVP

Joined

Feb 8, 2002

Messages

11,959

• Jun 30, 2003

• #9

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

 

Upvote 0

Tom Urtis

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,305

• Jun 30, 2003

• #10

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

 

Upvote 0