A tough one but the answer is probably NO !

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Richie - 16 Feb 2007 10:43 GMT

Hello again,
Last week I asked this question and was given the perfect answer. But I
have just realised I did not ask the complete question.

I had asked that was it possible to prevent someone inserting and deleting
rows on a spreadsheet and was given the answer by just unprotecting the cells
for the sheet and passwordprotect the sheet and that works.

The question I should have asked is the same as above but I need to be able
to use the filter. When a sheet is protected the filter is unavailable ?
Why is that ? The filter option only allows you to see selected data. Help
appreciated asap.

Regards

Signature

Niall

Reply to this Message

Paul B - 16 Feb 2007 11:50 GMT

Niall, when you protect the sheet under allow check use autofilter

Signature

Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

> Hello again,
> Last week I asked this question and was given the perfect answer. But I
[quoted text clipped - 10 lines]
>
> Regards

Reply to this Message

Richie - 16 Feb 2007 12:25 GMT

When I select protect sheet from the tools menu it gives me 3 options and
they are : Protect Worksheet for: Contents, Objects or Scenarios. It says
nothing about checking filters etc.

Signature

Niall

> Niall, when you protect the sheet under allow check use autofilter
>
[quoted text clipped - 15 lines]
> >
> > Regards

Reply to this Message

Richie - 16 Feb 2007 12:30 GMT

Paul B

Apologies - just realised I am using Excel 2000 and you are using Excel
2002+ and thats why I cannot click on allow filters.

Thanks again

Signature

Niall

> Niall, when you protect the sheet under allow check use autofilter
>
[quoted text clipped - 15 lines]
> >
> > Regards

Reply to this Message

Dave Peterson - 16 Feb 2007 14:33 GMT

If you already have the outline/subtotals/autofilter applied, you can protect
the worksheet in code (auto_open/workbook_open??).

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
'.EnableOutlining = True
.EnableAutoFilter = True
End With
End Sub

It needs to be reset each time you open the workbook. (Earlier versions of
excel don't remember it after closing the workbook. IIRC, xl2002+ will remember
the allow autofilter setting under tools|Protection|protect sheet.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

> Paul B
>
[quoted text clipped - 33 lines]
> > > --
> > > Niall

Signature

Dave Peterson

Reply to this Message