VB macro ascending & decending Alpha sorting on protected sheet

[statum]

ok, come on guys. I know some one can help me with this. This is a repost in
hopes that some one can help. 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, top 10, blanks,
non blanks etc..) work just fine. I am only having trouble "turning on" the
ascending and descending sort options. Note 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, AllowHyperlinks:=True
end sub

 

[Bernie Deitrick]

statum,

One other requirement for sorting to be allowed on a protected sheet is that
the cells that you want to allow to be sorted must be formatted as unlocked
(format / protect tab, uncheck locked).

HTH,
Bernie
MS Excel MVP

 

[statum]

Thanks Bernie. However, that defeats the purpose of the sheet being
protected. I do not want the user changing anything. I only want them to have
viewing, sorting and hyperlink priviledges. Writing code like:
Range("A1:AA10000").locked=false unlocks the cells and allows the user to
edit content. I want them to be able to sort alphabetically without having to
unprotect or unlock any cells. I do not understand why this is not possible.
If all other autofilter sorting options work, why don't these? After all,
they are just sort options.

 

[Bernie Deitrick]

Yes it does defeat the purpose.... one way round would be to use macros to sort, which unlock, sort,
and relock the range.

HTH,
Bernie
MS Excel MVP

 

[statum]

Right. If you notice from the code I provided, I am doing exactly that. The
user would need a way to start the macro simply, like a "click box". However,
I cannot put a "click box" on the sheet that am I protecting. Also, why
should I have to? The Autofilter drop down box already has the alpha sorts
defined and displayed along with all the other sorting options. This makes no
sense. A sort is a sort is a sort.