AutoFilter on Protected Worksheet Excel 2003

[aehan]

Has anyone else come across this, or am I doing something wrong? In Excel
2003 I protected a worksheet, enabling the AutoFilter in the list of
protection options. However, even though I did this, I couldn't turn
AutoFilter on. I looked up help, which said that enabling the AutoFilter in
the protection options is all you had to do. It also gave some code for use
in earlier versions. I used the code and the AutFilter works!! However, it
definitely doesn't simply by enabling it in the list of protection options.
Has anyone else come across this? the code is:

Sub test()

Results.Protect Password:="test", DrawingObjects:=True, _
contents:=True, Scenarios:=True, _
userinterfaceonly:=True
Results.EnableAutoFilter = True
End Sub

This solution works perfectly, but I am frustrated that the simple option
for Excel 2003 doesn't seem to work. I train other people who don't
necessarily understand code.

Cheers
Aehan

 

[Debra Dalgleish]

When you protect the worksheet, and enable AutoFilter in the list, it
allows users to use an existing AutoFilter, but they can't create a new one.

There's sample code here that checks for a filter when opening a
workbook, then protects the sheet:

http://www.contextures.com/xlautofilter03.html#Protect

 

[aehan]

Thanks for the reply. I tried it out and yes, if AutoFilter is applied
before protecting the sheet, the option to allow AutoFilter works. However,
Microsoft don't explain that. In their help on the subject, they say:

On the Tools menu in Excel 2003 or 2002, point to Protection and then click
Protect Sheet. The Protect Sheet dialog box appears.
From the Allow all users of this worksheet to list, select Use AutoFilter.

That is confusing for everyday users. Perhaps they should change it to
reflect your explanation. Thanks very much for your help.

Cheers

 

[Dave Peterson]

Lots of people agree with you.

In fact, if you're looking for explanations for other stuff, you may want to
bookmark Debra's site:
http://www.contextures.com/

Then you can visit her site first and be happy or visit it after you're
frustrated with MS's help <vbg>.

 

[aehan]

Thanks for the tip!

Lots of people agree with you.

In fact, if you're looking for explanations for other stuff, you may want to
bookmark Debra's site:
http://www.contextures.com/

Then you can visit her site first and be happy or visit it after you're
frustrated with MS's help <vbg>.

 

[David R]

Does xl2003 allow .EnableSorting like it does .EnableAutoFilter? I have a
worksheet with several protected columns (formulas). Locations will maintain
their own data so I can't unprotect the cells, but need to allow user
functionality, including filering and sorting rows.

I have a question about my auto_open script (below). Some of these
functions work and some do not. For example, deleting rows and sorting are
not allowed, but outlining, autofiltering, formatting rows is. Do you know
what might be causing that and/or what the possible solution is?

 

[aehan]

Hi

I'm no expert, which is why I ask the forum so many questions! However, you
can't delete a row or column that has a protected cell in it, the same thing
applies to sorts. Have you tried validating the formula rather than
protecting it? That way the formula can't be overwritten, but all the
functionality of Excel remains. That's what I tend to do, and you don't need
any code!

Best wishes
Aehan

 

[David R]

Interesting option. How do you 'protect' that validation format so it can't
be changed? Also, doesn't allow accidental deletion of the entire formula
(zero value)?