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").Ra nge.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

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" wrote:

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").Ra nge.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

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" wrote:

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" wrote:

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").Ra nge.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

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" wrote:

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" wrote:

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" wrote:

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").Ra nge.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

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" wrote:

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" wrote:

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" wrote:

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").Ra nge.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

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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").Ra nge.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

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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").Ra nge.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

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.
--
If this post helps click Yes
---------------
Peggy Shepard


"OMER" wrote:

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").Ra nge.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