How do we configure our worksheet so that we can turn protection ON, but 
still enable the table filter buttons in order to sort columns in a table?

No matter what settings we select for protection, including the sort and 
auto-filter options, we cannot sort tables on a protected sheet.  What is 
the secret to allowing table sorts on a protected sheet? 

Generally one has code to unprotect, code to do the sort, then re-protect.

By "tables" do you a real Table created by Insert>Table?

I can provide code that turns off protection when you click inside a Table.

Click outside the Table and protection goes back on.

Is this something you could work with?


Gord Dibben  MS Excel MVP

On Sat, 10 Apr 2010 14:22:00 -0600, "Blue Max" <mailrichard@msn.com> wrote:

>How do we configure our worksheet so that we can turn protection ON, but 
>still enable the table filter buttons in order to sort columns in a table?
>
>No matter what settings we select for protection, including the sort and 
>auto-filter options, we cannot sort tables on a protected sheet.  What is 
>the secret to allowing table sorts on a protected sheet? 

If Gord's suggestion and offer of assistance isn't what you need, then things 
get a little flakey for you:  In order to sort on a protected sheet, all of 
the cells that may potentially be involved in the sort must be unlocked.  
Quite often this defeats at least part of the purpose of protecting the sheet 
in the first place.  As Gord said, the typical way to handle this enigma is 
via VBA code that unprotects the sheet, does the sort and then puts the sheet 
back into protected mode.

"Blue Max" wrote:

> How do we configure our worksheet so that we can turn protection ON, but 
> still enable the table filter buttons in order to sort columns in a table?
> 
> No matter what settings we select for protection, including the sort and 
> auto-filter options, we cannot sort tables on a protected sheet.  What is 
> the secret to allowing table sorts on a protected sheet? 
> 
> .
> 

Record a macro for your sort, then see this page;
http://www.ozgrid.com/VBA/excel-macro-protected-sheet.htm

The UserInterfaceOnly is my preference in case the Macro bugs out leaving 
your Worksheet unprotected.




-- 
Regards
Dave Hawley
www.ozgrid.com
"Blue Max" <mailrichard@msn.com> wrote in message 
news:O7xsutO2KHA.5212@TK2MSFTNGP04.phx.gbl...
> How do we configure our worksheet so that we can turn protection ON, but 
> still enable the table filter buttons in order to sort columns in a table?
>
> No matter what settings we select for protection, including the sort and 
> auto-filter options, we cannot sort tables on a protected sheet.  What is 
> the secret to allowing table sorts on a protected sheet? 

Thank you, Gord.  I would be interested in a sample of the code.  I have 
created code before to protect and unprotect a sheet tied to a button or 
cell, but haven't ever tied it to selecting a table or not.  That may be a 
useful approach to this problem.  Nevertheless, can you answer the 
following:

As I indicated in the original message, when a worksheet is protected the 
user can choose options to allow auto-filtering or sorts.  When these 
options are chosen, why is the program not allowing the user to sort the 
table using the filter buttons or a sort?  One would infer from the 
protection options selected that the sort would be allowed!

Thanks,

Richard

****************************
"Gord Dibben" <gorddibbATshawDOTca> wrote in message 
news:nrn1s5ll7fcearto8nv219a2tc39ute4ck@4ax.com...
> Generally one has code to unprotect, code to do the sort, then re-protect.
>
> By "tables" do you a real Table created by Insert>Table?
>
> I can provide code that turns off protection when you click inside a 
> Table.
>
> Click outside the Table and protection goes back on.
>
> Is this something you could work with?
>
>
> Gord Dibben  MS Excel MVP
>
> On Sat, 10 Apr 2010 14:22:00 -0600, "Blue Max" <mailrichard@msn.com> 
> wrote:
>
>>How do we configure our worksheet so that we can turn protection ON, but
>>still enable the table filter buttons in order to sort columns in a table?
>>
>>No matter what settings we select for protection, including the sort and
>>auto-filter options, we cannot sort tables on a protected sheet.  What is
>>the secret to allowing table sorts on a protected sheet?
> 

I agree that the help on allowing sorting and filtering is not too detailed
and the implication is that you need only allow these options to be enabled
when protecting.

Not exactly true.

A protected sheet can be sorted or filtered but only under certain
conditions.

If the entire range to be sorted contains unlocked cells, you can sort that
range on a protected sheet.

If the Autofilter is enabled prior to protecting the sheet, autofilter can
be used.

Both options must be enabled when protecting the sheet.

Code to select a Table and unprotect the sheet.

Note that all cell protection within the Table is is removed.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Dim objlist As ListObject
   Set objlist = Me.ListObjects(1)   'adjust the (1) if needed
   On Error GoTo ws_exit:
    Application.EnableEvents = False
     If Not Intersect(Target, objlist.Range) Is Nothing Then
         Me.Unprotect Password:="justme"
         Else
         With Me
            .Protect Password:="justme"
            .EnableSelection = xlNoRestrictions
         End With
    End If
ws_exit:
    Application.EnableEvents = True
End Sub


Gord

On Thu, 22 Apr 2010 02:44:15 -0600, "Blue Max" <mailrichard@msn.com> wrote:

>Thank you, Gord.  I would be interested in a sample of the code.  I have 
>created code before to protect and unprotect a sheet tied to a button or 
>cell, but haven't ever tied it to selecting a table or not.  That may be a 
>useful approach to this problem.  Nevertheless, can you answer the 
>following:
>
>As I indicated in the original message, when a worksheet is protected the 
>user can choose options to allow auto-filtering or sorts.  When these 
>options are chosen, why is the program not allowing the user to sort the 
>table using the filter buttons or a sort?  One would infer from the 
>protection options selected that the sort would be allowed!
>
>Thanks,
>
>Richard
>
>****************************
>"Gord Dibben" <gorddibbATshawDOTca> wrote in message 
>news:nrn1s5ll7fcearto8nv219a2tc39ute4ck@4ax.com...
>> Generally one has code to unprotect, code to do the sort, then re-protect.
>>
>> By "tables" do you a real Table created by Insert>Table?
>>
>> I can provide code that turns off protection when you click inside a 
>> Table.
>>
>> Click outside the Table and protection goes back on.
>>
>> Is this something you could work with?
>>
>>
>> Gord Dibben  MS Excel MVP
>>
>> On Sat, 10 Apr 2010 14:22:00 -0600, "Blue Max" <mailrichard@msn.com> 
>> wrote:
>>
>>>How do we configure our worksheet so that we can turn protection ON, but
>>>still enable the table filter buttons in order to sort columns in a table?
>>>
>>>No matter what settings we select for protection, including the sort and
>>>auto-filter options, we cannot sort tables on a protected sheet.  What is
>>>the secret to allowing table sorts on a protected sheet?
>> 

Thank you, Dave, some interesting features.  I appreciate your reply and 
will take a look at this option.

Thanks,

Richard

*****************
"ozgrid.com" <dave@ozgrid.com> wrote in message 
news:BBBB76E2-C991-41B4-84F8-8A191101E21A@microsoft.com...
> Record a macro for your sort, then see this page;
> http://www.ozgrid.com/VBA/excel-macro-protected-sheet.htm
>
> The UserInterfaceOnly is my preference in case the Macro bugs out leaving 
> your Worksheet unprotected.
>
>
>
>
> -- 
> Regards
> Dave Hawley
> www.ozgrid.com
> "Blue Max" <mailrichard@msn.com> wrote in message 
> news:O7xsutO2KHA.5212@TK2MSFTNGP04.phx.gbl...
>> How do we configure our worksheet so that we can turn protection ON, but 
>> still enable the table filter buttons in order to sort columns in a 
>> table?
>>
>> No matter what settings we select for protection, including the sort and 
>> auto-filter options, we cannot sort tables on a protected sheet.  What is 
>> the secret to allowing table sorts on a protected sheet?
> 

Thank you, Gord, I appreciate the guidelines and the suggested code.  Wish 
the protection exceptions would have covered this case, but they apparently 
do not.  Oh well, perhaps in a future version.

****************
"Gord Dibben" <gorddibbATshawDOTca> wrote in message 
news:gh81t5hdc9os2gofn519f345cfq7g7086c@4ax.com...
>I agree that the help on allowing sorting and filtering is not too detailed
> and the implication is that you need only allow these options to be 
> enabled
> when protecting.
>
> Not exactly true.
>
> A protected sheet can be sorted or filtered but only under certain
> conditions.
>
> If the entire range to be sorted contains unlocked cells, you can sort 
> that
> range on a protected sheet.
>
> If the Autofilter is enabled prior to protecting the sheet, autofilter can
> be used.
>
> Both options must be enabled when protecting the sheet.
>
> Code to select a Table and unprotect the sheet.
>
> Note that all cell protection within the Table is is removed.
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>   Dim objlist As ListObject
>   Set objlist = Me.ListObjects(1)   'adjust the (1) if needed
>   On Error GoTo ws_exit:
>    Application.EnableEvents = False
>     If Not Intersect(Target, objlist.Range) Is Nothing Then
>         Me.Unprotect Password:="justme"
>         Else
>         With Me
>            .Protect Password:="justme"
>            .EnableSelection = xlNoRestrictions
>         End With
>    End If
> ws_exit:
>    Application.EnableEvents = True
> End Sub
>
>
> Gord
>
> On Thu, 22 Apr 2010 02:44:15 -0600, "Blue Max" <mailrichard@msn.com> 
> wrote:
>
>>Thank you, Gord.  I would be interested in a sample of the code.  I have
>>created code before to protect and unprotect a sheet tied to a button or
>>cell, but haven't ever tied it to selecting a table or not.  That may be a
>>useful approach to this problem.  Nevertheless, can you answer the
>>following:
>>
>>As I indicated in the original message, when a worksheet is protected the
>>user can choose options to allow auto-filtering or sorts.  When these
>>options are chosen, why is the program not allowing the user to sort the
>>table using the filter buttons or a sort?  One would infer from the
>>protection options selected that the sort would be allowed!
>>
>>Thanks,
>>
>>Richard
>>
>>****************************
>>"Gord Dibben" <gorddibbATshawDOTca> wrote in message
>>news:nrn1s5ll7fcearto8nv219a2tc39ute4ck@4ax.com...
>>> Generally one has code to unprotect, code to do the sort, then 
>>> re-protect.
>>>
>>> By "tables" do you a real Table created by Insert>Table?
>>>
>>> I can provide code that turns off protection when you click inside a
>>> Table.
>>>
>>> Click outside the Table and protection goes back on.
>>>
>>> Is this something you could work with?
>>>
>>>
>>> Gord Dibben  MS Excel MVP
>>>
>>> On Sat, 10 Apr 2010 14:22:00 -0600, "Blue Max" <mailrichard@msn.com>
>>> wrote:
>>>
>>>>How do we configure our worksheet so that we can turn protection ON, but
>>>>still enable the table filter buttons in order to sort columns in a 
>>>>table?
>>>>
>>>>No matter what settings we select for protection, including the sort and
>>>>auto-filter options, we cannot sort tables on a protected sheet.  What 
>>>>is
>>>>the secret to allowing table sorts on a protected sheet?
>>>
>