Unable to sort protected table

[OMER]

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?

 

[Andy Smith]

Unprotect the sheet, sort, then protect.

 

[OMER]

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

 

[Gord Dibben]

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

 

[OMER]

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]

You have created a Table.

You have locked some of the cells within that Table.

You have protected the sheet.

You want to sort or filter on columns within that table.

I don't know how you are going to this without unprotecting the sheet.

You cannot lock part of a Table without sheet protection enabled.


Gord

 

[OMER]

Hola Gord,
Actually, when you protect the worksheet, you have the option to allow the
user to filter and sort. I have those enabled and, as I say, I can filter
(with the protected worksheet) but cannot sort.
I tried this with simple ranges (that is, not tables) and it does work. But
with tables it doesn't.
I guess that either a bug or an undocumented lack of capability.
REgards,
Oscar

 

[Gord Dibben]

You can sort on a protected sheet as long as your sort range does not
contain any locked cells.

Example...................A1:G20 cells are all unlocked and sheet protected.

You can sort that range.

If one cell within that range is locked, you cannot sort that range.

I don't think this is documented but stands to reason that if you lock a
cell you should not be able to move it, which sort wants to do.


Gord

 

[OMER]

I understand that, but again.
This situation applies only to tables. If I have a normal range, and within
that range (A1:G20 in your example) is a normal range, even with the sheet
protected I'm able to sort.

The problem when I define a table (not pivoy table) for that same range.
Then I cannot sort.

Make sense?
Regards,
OMER

 

[Gord Dibben]

Does your Table range contain even one locked cell?

If so, same rules apply.


Gord