A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Sorting tables on protected worksheets



 
 
Thread Tools Display Modes
  #1  
Old April 10th, 2010, 09:22 PM posted to microsoft.public.excel.newusers
Blue Max
external usenet poster
 
Posts: 301
Default Sorting tables on protected worksheets

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?

  #2  
Old April 10th, 2010, 09:56 PM posted to microsoft.public.excel.newusers
Gord Dibben
external usenet poster
 
Posts: 20,252
Default Sorting tables on protected worksheets

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

By "tables" do you a real Table created by InsertTable?

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" 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?


  #3  
Old April 11th, 2010, 01:31 AM posted to microsoft.public.excel.newusers
JLatham
external usenet poster
 
Posts: 1,896
Default Sorting tables on protected worksheets

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?

.

  #4  
Old April 11th, 2010, 03:15 AM posted to microsoft.public.excel.newusers
ozgrid.com
external usenet poster
 
Posts: 328
Default Sorting tables on protected worksheets

Record a macro for your sort, then see this page;
http://www.ozgrid.com/VBA/excel-macr...cted-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" wrote in message
...
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?


  #5  
Old April 29th, 2010, 04:42 PM posted to microsoft.public.excel.newusers
Blue Max
external usenet poster
 
Posts: 301
Default Sorting tables on protected worksheets

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

Thanks,

Richard

*****************
"ozgrid.com" wrote in message
...
Record a macro for your sort, then see this page;
http://www.ozgrid.com/VBA/excel-macr...cted-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" wrote in message
...
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?



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:31 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.