Pivot Table on protected worksheet -

O

OMER

Hola,
I'm using Excel 2007.

I have a table defined on a protected worksheet. It works great when the
worksheet is unprotected, but when I protect the worksheet (within a macro)
and then try to sort, filter, etc. (outside of the macro), I get the
following error message:

"The cell or chart you are trying to change is protected and therefore
read-only."

I've read similar entries within this site, and it looks that I´m still
missing something. Here is the code:

'
Range("Dashboard_Data_Table[[#Headers],[Project ID]]").Select
ActiveSheet.ListObjects("Dashboard_Data_Table").Range.AutoFilter
Field:=3, _
Criteria1:="<>"
Range("A1").Select
'
ActiveSheet.Protect DrawingObjects:=False, contents:=True, Scenarios:= _
True, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True, userInterfaceOnly:=True,
Password:="password1"
'

Help is higly appreciated.
Regards
OMER
 
P

pshepard

Hi Omer,

The following works for me -

Macro1 Macro
'
Sheets("Sheet1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
End Sub
 
O

OMER

Thank you Peggy.
I had all "Protect" settings set to True (see my code below) and still
getting the same message.

Any other ideas?


pshepard said:
Hi Omer,

The following works for me -

Macro1 Macro
'
Sheets("Sheet1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
End Sub
--
If this post helps click Yes
---------------
Peggy Shepard


OMER said:
Hola,
I'm using Excel 2007.

I have a table defined on a protected worksheet. It works great when the
worksheet is unprotected, but when I protect the worksheet (within a macro)
and then try to sort, filter, etc. (outside of the macro), I get the
following error message:

"The cell or chart you are trying to change is protected and therefore
read-only."

I've read similar entries within this site, and it looks that I´m still
missing something. Here is the code:

'
Range("Dashboard_Data_Table[[#Headers],[Project ID]]").Select
ActiveSheet.ListObjects("Dashboard_Data_Table").Range.AutoFilter
Field:=3, _
Criteria1:="<>"
Range("A1").Select
'
ActiveSheet.Protect DrawingObjects:=False, contents:=True, Scenarios:= _
True, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True, userInterfaceOnly:=True,
Password:="password1"
'

Help is higly appreciated.
Regards
OMER
Click to expand...
Click to expand...
 
P

pshepard

Hi Omer,

Actually I was only able to filter - not sort. So the ability to allow sort
when the sheet is protected does not work for me either.

Sorry - I think this may be a bug
--
If this post helps click Yes
---------------
Peggy Shepard


OMER said:
Thank you Peggy.
I had all "Protect" settings set to True (see my code below) and still
getting the same message.

Any other ideas?


pshepard said:
Hi Omer,

The following works for me -

Macro1 Macro
'
Sheets("Sheet1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
End Sub
--
If this post helps click Yes
---------------
Peggy Shepard


OMER said:
Hola,
I'm using Excel 2007.

I have a table defined on a protected worksheet. It works great when the
worksheet is unprotected, but when I protect the worksheet (within a macro)
and then try to sort, filter, etc. (outside of the macro), I get the
following error message:

"The cell or chart you are trying to change is protected and therefore
read-only."

I've read similar entries within this site, and it looks that I´m still
missing something. Here is the code:

'
Range("Dashboard_Data_Table[[#Headers],[Project ID]]").Select
ActiveSheet.ListObjects("Dashboard_Data_Table").Range.AutoFilter
Field:=3, _
Criteria1:="<>"
Range("A1").Select
'
ActiveSheet.Protect DrawingObjects:=False, contents:=True, Scenarios:= _
True, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True, userInterfaceOnly:=True,
Password:="password1"
'

Help is higly appreciated.
Regards
OMER
Click to expand...
Click to expand...
Click to expand...
 
P

pshepard

Hi Omer,

The following allows filtering and sorting for pivot tables - I was not
getting a table to allow sorting is where I believe there is a bug.

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True,
AllowUsingPivotTables:=True
--
If this post helps click Yes
---------------
Peggy Shepard


OMER said:
Thank you Peggy.
I had all "Protect" settings set to True (see my code below) and still
getting the same message.

Any other ideas?


pshepard said:
Hi Omer,

The following works for me -

Macro1 Macro
'
Sheets("Sheet1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
End Sub
--
If this post helps click Yes
---------------
Peggy Shepard


OMER said:
Hola,
I'm using Excel 2007.

I have a table defined on a protected worksheet. It works great when the
worksheet is unprotected, but when I protect the worksheet (within a macro)
and then try to sort, filter, etc. (outside of the macro), I get the
following error message:

"The cell or chart you are trying to change is protected and therefore
read-only."

I've read similar entries within this site, and it looks that I´m still
missing something. Here is the code:

'
Range("Dashboard_Data_Table[[#Headers],[Project ID]]").Select
ActiveSheet.ListObjects("Dashboard_Data_Table").Range.AutoFilter
Field:=3, _
Criteria1:="<>"
Range("A1").Select
'
ActiveSheet.Protect DrawingObjects:=False, contents:=True, Scenarios:= _
True, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True, userInterfaceOnly:=True,
Password:="password1"
'

Help is higly appreciated.
Regards
OMER
Click to expand...
Click to expand...
Click to expand...
 
O

OMER

Thabk You Peggy.
I guess there is something wrong with my table, since the parameters are
correct and I'm still unable to sort (even with the AllowSorting:=True). I
can do filtering but not sorting.
I'll try to re-create the table and try again.

Thank you for your help.
Regards,
OMER

pshepard said:
Hi Omer,

The following allows filtering and sorting for pivot tables - I was not
getting a table to allow sorting is where I believe there is a bug.

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True,
AllowUsingPivotTables:=True
--
If this post helps click Yes
---------------
Peggy Shepard


OMER said:
Thank you Peggy.
I had all "Protect" settings set to True (see my code below) and still
getting the same message.

Any other ideas?


pshepard said:
Hi Omer,

The following works for me -

Macro1 Macro
'
Sheets("Sheet1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
End Sub
--
If this post helps click Yes
---------------
Peggy Shepard


:

Hola,
I'm using Excel 2007.

I have a table defined on a protected worksheet. It works great when the
worksheet is unprotected, but when I protect the worksheet (within a macro)
and then try to sort, filter, etc. (outside of the macro), I get the
following error message:

"The cell or chart you are trying to change is protected and therefore
read-only."

I've read similar entries within this site, and it looks that I´m still
missing something. Here is the code:

'
Range("Dashboard_Data_Table[[#Headers],[Project ID]]").Select
ActiveSheet.ListObjects("Dashboard_Data_Table").Range.AutoFilter
Field:=3, _
Criteria1:="<>"
Range("A1").Select
'
ActiveSheet.Protect DrawingObjects:=False, contents:=True, Scenarios:= _
True, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True, userInterfaceOnly:=True,
Password:="password1"
'

Help is higly appreciated.
Regards
OMER
Click to expand...
Click to expand...
Click to expand...
 
P

pshepard

Hi Omer,

I posted the issue of sort on a protected worksheet as a microsoft comment -
and told them that I believe this to be a bug. Thank you for your last reply,
I appreciate it!
--
If this post helps click Yes
---------------
Peggy Shepard


OMER said:
Thabk You Peggy.
I guess there is something wrong with my table, since the parameters are
correct and I'm still unable to sort (even with the AllowSorting:=True). I
can do filtering but not sorting.
I'll try to re-create the table and try again.

Thank you for your help.
Regards,
OMER

pshepard said:
Hi Omer,

The following allows filtering and sorting for pivot tables - I was not
getting a table to allow sorting is where I believe there is a bug.

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True,
AllowUsingPivotTables:=True
--
If this post helps click Yes
---------------
Peggy Shepard


OMER said:
Thank you Peggy.
I had all "Protect" settings set to True (see my code below) and still
getting the same message.

Any other ideas?


:

Hi Omer,

The following works for me -

Macro1 Macro
'
Sheets("Sheet1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
End Sub
--
If this post helps click Yes
---------------
Peggy Shepard


:

Hola,
I'm using Excel 2007.

I have a table defined on a protected worksheet. It works great when the
worksheet is unprotected, but when I protect the worksheet (within a macro)
and then try to sort, filter, etc. (outside of the macro), I get the
following error message:

"The cell or chart you are trying to change is protected and therefore
read-only."

I've read similar entries within this site, and it looks that I´m still
missing something. Here is the code:

'
Range("Dashboard_Data_Table[[#Headers],[Project ID]]").Select
ActiveSheet.ListObjects("Dashboard_Data_Table").Range.AutoFilter
Field:=3, _
Criteria1:="<>"
Range("A1").Select
'
ActiveSheet.Protect DrawingObjects:=False, contents:=True, Scenarios:= _
True, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True, userInterfaceOnly:=True,
Password:="password1"
'

Help is higly appreciated.
Regards
OMER
Click to expand...
Click to expand...
Click to expand...
 
O

OMER

Hola Peggy,
I realized that the table I'm talking about is NOT a pivot table, but an
inserted table.
I'm still having the same problem though, and I just wondered if this type
of table has the same treatment as a ´pivot table, in terms of protection.
Any additional insight?
Regards,
Oscar

pshepard said:
Hi Omer,

I posted the issue of sort on a protected worksheet as a microsoft comment -
and told them that I believe this to be a bug. Thank you for your last reply,
I appreciate it!
--
If this post helps click Yes
---------------
Peggy Shepard


OMER said:
Thabk You Peggy.
I guess there is something wrong with my table, since the parameters are
correct and I'm still unable to sort (even with the AllowSorting:=True). I
can do filtering but not sorting.
I'll try to re-create the table and try again.

Thank you for your help.
Regards,
OMER

pshepard said:
Hi Omer,

The following allows filtering and sorting for pivot tables - I was not
getting a table to allow sorting is where I believe there is a bug.

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True,
AllowUsingPivotTables:=True
--
If this post helps click Yes
---------------
Peggy Shepard


:

Thank you Peggy.
I had all "Protect" settings set to True (see my code below) and still
getting the same message.

Any other ideas?


:

Hi Omer,

The following works for me -

Macro1 Macro
'
Sheets("Sheet1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
End Sub
--
If this post helps click Yes
---------------
Peggy Shepard


:

Hola,
I'm using Excel 2007.

I have a table defined on a protected worksheet. It works great when the
worksheet is unprotected, but when I protect the worksheet (within a macro)
and then try to sort, filter, etc. (outside of the macro), I get the
following error message:

"The cell or chart you are trying to change is protected and therefore
read-only."

I've read similar entries within this site, and it looks that I´m still
missing something. Here is the code:

'
Range("Dashboard_Data_Table[[#Headers],[Project ID]]").Select
ActiveSheet.ListObjects("Dashboard_Data_Table").Range.AutoFilter
Field:=3, _
Criteria1:="<>"
Range("A1").Select
'
ActiveSheet.Protect DrawingObjects:=False, contents:=True, Scenarios:= _
True, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True, userInterfaceOnly:=True,
Password:="password1"
'

Help is higly appreciated.
Regards
OMER
Click to expand...
Click to expand...
Click to expand...
 
P

pshepard

Hi Omer,

I found that if cells are formatted/protection/unlocked, when the sheet is
protected and sort is not selected - then cells that aren't locked will still
not sort - so sort is necessary to allow even for cells that are unlocked -
not what I would expect. To me the logical behavior for unlocked cells would
be that they could be sorted as well as edited.
--
If this post helps click Yes
---------------
Peggy Shepard


OMER said:
Hola Peggy,
I realized that the table I'm talking about is NOT a pivot table, but an
inserted table.
I'm still having the same problem though, and I just wondered if this type
of table has the same treatment as a ´pivot table, in terms of protection.
Any additional insight?
Regards,
Oscar

pshepard said:
Hi Omer,

I posted the issue of sort on a protected worksheet as a microsoft comment -
and told them that I believe this to be a bug. Thank you for your last reply,
I appreciate it!
--
If this post helps click Yes
---------------
Peggy Shepard


OMER said:
Thabk You Peggy.
I guess there is something wrong with my table, since the parameters are
correct and I'm still unable to sort (even with the AllowSorting:=True). I
can do filtering but not sorting.
I'll try to re-create the table and try again.

Thank you for your help.
Regards,
OMER

:

Hi Omer,

The following allows filtering and sorting for pivot tables - I was not
getting a table to allow sorting is where I believe there is a bug.

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True,
AllowUsingPivotTables:=True
--
If this post helps click Yes
---------------
Peggy Shepard


:

Thank you Peggy.
I had all "Protect" settings set to True (see my code below) and still
getting the same message.

Any other ideas?


:

Hi Omer,

The following works for me -

Macro1 Macro
'
Sheets("Sheet1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
End Sub
--
If this post helps click Yes
---------------
Peggy Shepard


:

Hola,
I'm using Excel 2007.

I have a table defined on a protected worksheet. It works great when the
worksheet is unprotected, but when I protect the worksheet (within a macro)
and then try to sort, filter, etc. (outside of the macro), I get the
following error message:

"The cell or chart you are trying to change is protected and therefore
read-only."

I've read similar entries within this site, and it looks that I´m still
missing something. Here is the code:

'
Range("Dashboard_Data_Table[[#Headers],[Project ID]]").Select
ActiveSheet.ListObjects("Dashboard_Data_Table").Range.AutoFilter
Field:=3, _
Criteria1:="<>"
Range("A1").Select
'
ActiveSheet.Protect DrawingObjects:=False, contents:=True, Scenarios:= _
True, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True, userInterfaceOnly:=True,
Password:="password1"
'

Help is higly appreciated.
Regards
OMER
Click to expand...
Click to expand...
Click to expand...
 
O

OMER

I understand that. What is weird is that I have Sort and Filter enabled (see
below:)

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True

Regards,
Oscar

pshepard said:
Hi Omer,

I found that if cells are formatted/protection/unlocked, when the sheet is
protected and sort is not selected - then cells that aren't locked will still
not sort - so sort is necessary to allow even for cells that are unlocked -
not what I would expect. To me the logical behavior for unlocked cells would
be that they could be sorted as well as edited.
--
If this post helps click Yes
---------------
Peggy Shepard


OMER said:
Hola Peggy,
I realized that the table I'm talking about is NOT a pivot table, but an
inserted table.
I'm still having the same problem though, and I just wondered if this type
of table has the same treatment as a ´pivot table, in terms of protection.
Any additional insight?
Regards,
Oscar

pshepard said:
Hi Omer,

I posted the issue of sort on a protected worksheet as a microsoft comment -
and told them that I believe this to be a bug. Thank you for your last reply,
I appreciate it!
--
If this post helps click Yes
---------------
Peggy Shepard


:

Thabk You Peggy.
I guess there is something wrong with my table, since the parameters are
correct and I'm still unable to sort (even with the AllowSorting:=True). I
can do filtering but not sorting.
I'll try to re-create the table and try again.

Thank you for your help.
Regards,
OMER

:

Hi Omer,

The following allows filtering and sorting for pivot tables - I was not
getting a table to allow sorting is where I believe there is a bug.

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True,
AllowUsingPivotTables:=True
--
If this post helps click Yes
---------------
Peggy Shepard


:

Thank you Peggy.
I had all "Protect" settings set to True (see my code below) and still
getting the same message.

Any other ideas?


:

Hi Omer,

The following works for me -

Macro1 Macro
'
Sheets("Sheet1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
End Sub
--
If this post helps click Yes
---------------
Peggy Shepard


:

Hola,
I'm using Excel 2007.

I have a table defined on a protected worksheet. It works great when the
worksheet is unprotected, but when I protect the worksheet (within a macro)
and then try to sort, filter, etc. (outside of the macro), I get the
following error message:

"The cell or chart you are trying to change is protected and therefore
read-only."

I've read similar entries within this site, and it looks that I´m still
missing something. Here is the code:

'
Range("Dashboard_Data_Table[[#Headers],[Project ID]]").Select
ActiveSheet.ListObjects("Dashboard_Data_Table").Range.AutoFilter
Field:=3, _
Criteria1:="<>"
Range("A1").Select
'
ActiveSheet.Protect DrawingObjects:=False, contents:=True, Scenarios:= _
True, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True, userInterfaceOnly:=True,
Password:="password1"
'

Help is higly appreciated.
Regards
OMER
Click to expand...
Click to expand...
Click to expand...
 
P

pshepard

You also have to go into the cells that need to be sorted, and deselect lock
for those cells - which means that a user will be able to edit them.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Unable to sort protected table 9
Sorting a protected worksheet 1
Macro to open view with a password 3
Editing a Chart on a protected worksheet 3
Hiding Rows with Blank cells 5
Password protection worksheet 1
How to extend the life of a variable from one Private Sub routine to another? 2
Excel 2003 macro for turning on AtoZ autofiltering on a protected 5

Top