View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
omer omer is offline
external usenet poster
 
Posts: 74
Default Unable to sort protected table

Hola Gord,
This work if you sort within a macro.
The situation is that I've activated the auto-filtering for columns (several
columns) and the user can filter and sort on any of them. Some columns have
formulas, that's why I need to protect the worksheet.
The curious thing is that I can filter even with protection On but when I
try to sort, it wouldn't let me.
If I do this on a named ranged, I can do it. If I do it with a table (Not
pivot table), then it doesn't work.

Any other ideas?
Regards,
OMER

"Gord Dibben" wrote:

Do it all in the macro which users run from a button or menu.

Sub sortit()
ActiveSheet.Unprotect Password:="justme"

'your sorting code goes here and does the sort

ActiveSheet.Protect Password:="justme"
End Sub

You can hide the code from users by locking the project with a unique
password.


Gord Dibben MS Excel MVP

On Fri, 18 Sep 2009 13:26:02 -0700, OMER
wrote:

Hola Andy.
The workbook I'm talking about is being used by many other users.
To maintain integrity, they are not allow to unprotect the worksheet.
Any other suggestions?
Regards,
OMER

"Andy Smith" wrote:

Unprotect the sheet, sort, then protect.


--
* Please click Yes if this was helpful *
Andy Smith
Senior Systems Analyst
Standard & Poor''s, NYC



"OMER" wrote:

Hola,

I'm using Excel 2007.
I have an Inserted Table (not a pivot table) on a worksheet and have
autofilter on.
I protected some formulas on the table and then protected the worksheet with
the following command:

ActiveSheet.Protect DrawingObjects:=False, contents:=True, Scenarios:= _
True, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True, userInterfaceOnly:=True,
Password:="password1"

The problem is that everytime I need to sort a column I get the following
message:

"The cell or chart you are trying to change is protected and therefore
read-only. Unprotect the worksheet by going to ........"

As you can imaging, this is not acceptable, since the whole purpose is to
protect the formulas being seen/changed by the user.

Any suggestions?