Excel 2003 macro for turning on AtoZ autofiltering on a protected

[statum]

I am trying to figure out how to allow a user to use the sort ascending and
sort descending options that are displayed when the user clicks the drop down
icon in a autofiltered cell. The other options (all, custom, etc..) work just
fine. I am only having trouble "turning" on the ascending and descending sort
options. Not that the worksheet is protected. Here is an excerpt of my
code.....thanks in advance for the help! Note that AutoFiltering is turned
on....
sub Workbook_Open()
Sheets("WORKLOG").Unprotect
..
..
..
do stuff.....
..
..
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowSorting:=True, AllowFiltering:=True,
AllowUsingPivotTables:=True
end sub

 

[Allllen]

Hi statum

I had a play with this in excel 2003 and it worked fine.

I guess the question you should ask yourself is whether you can make it work
first of all *without* using a macro. Play with it in a very simple blank
workbook. Afterwards, getting the macro right is not such a problem.

When the sheet is protected as you have done it below, excel will let you
use the autofilter. But of course before you protect the sheet you need to
make sure that any cells or rows that you might want to sort are unlocked.
Select all the cells in your data, do Format > Cells and unlock them on the
Protection tab.

It is enough for one cell in your data to be locked to mean that you can't
do the sort from the autofilter when the sheet is protected. I reckon this
is what is giving you a headache.

 

[Allllen]

Before protecting the sheet, ensure none of the cells in the range you want
to filter are locked. Then it should work.

 

[Gord Dibben]

Also Autofilter must be enabled before protecting the sheet.


Gord Dibben MS Excel MVP

 

[statum]

Thanks for the reply. Can you put your statements in code format so I can see
exactly what you are talking about? I have tried to use
Range("a1:aa10000").locked=false and this did not work. as a matter of fact
it all it did was allow the user to write in the cells. Which I do not want
them to be able to do. I do not understand why the sort ascending and
descending options are not allowed? I have set the allowsorting and
allowfiltering to true when protecting the sheet from my macro, so why can't
the user sort using the Ascending or Descending options in the drop down box
of the autofilter?

 

[statum]

Thanks, I did do this in the macro before protecting it....