sorting data on protected worksheet

[Guest]

Excel 2003 worksheet
I have data in B1 being the header row through to Y366.
I have selected all cells and unlocked them. I have then selected B1:Y1 and
locked them as they are my header row.
I then went tools,protection,allow users to edit and input a range of
B2:Y366. did not want to give password permission, clicked apply and then
clicked protect sheet and checked unlocked cells and sort and then OK.
When I then go to sort data it tells me "The cell or chart you are trying to
change is protected and therefore read-only."
To modify a protected cell or chart, first remove protection using the
Unprotect the sheet command (Tools menu, Protection submenu)> You may be
prompted for a password.
What am I doing wrong. Should it not sort even though the protection is on
if I have a user range input?
Someone please help.
I did have columns hidden but they have all be displayed prior to the first
step as above.
I want it protected as I have formula's that will be tucked away on my
hidden columns that I don't want people to get to and wipe by mistake.
Thanks
Sue

 

[Dave Peterson]

I tried this and it worked ok for me.

Can you try one more time?

 

[Guest]

Hi Dave,
I have just done the process again and it still tells me "The cell or
chart...read only.
Is there a step I'm missing.
Thanks
Sue

 

[Guest]

Dave,
I have just tried the above steps without locking the header row cells and
it protects and sorts fine so I suppose that is better than nothing. Is that
the only choice I have for this scenario or is there a way to lock my
headings row (which is only 1 row) and protect and sort?

 

[Dave Peterson]

I couldn't duplicate your problem.

But I selected the range (B2:y366). How did you select the range to sort? Did
you select the whole column(s)?

If you did, then that's the problem.

(I also unchecked the "select locked cells" on the worksheet protection
dialog--so I couldn't even select those header cells.)

 

[Guest]

Hi Dave,
I have tried again with the same result. I did:
I did select all first and unlocked all cells.
Then highlighted cells B1:Y1 and locked only those cells (header row)
Tools>protection>allow users to edit ranges
Using collapse box typed in B2:Y366 (also previously did it by highlighting
B2:Y366 before going to protection>allow users to edit ranges)
No password
Apply
Protect sheet
Checked boxes are select unlocked cells and sort in the lower portion of
window otherwise all are unchecked. Left the top box of "Protect the
worksheet and contents of locked cells" checked. So in total I have three
boxes on the protect sheet window checked.
Ok
(All columns are unhidden at the beginning of all this process)
Then I click on cell L2, click on A-Z key button and it comes up with "This
cell or chart ... is read only." It says the same thing if I go to Data>Sort
or click the Z-A button.
Is there something somewhere else that I need to go to first or last to tell
it allow sort even though protected. It seems like the sort box checked in
the protection box is not working. But it works okay if I don't lock my
header row. The stages above are exactly what I am doing. As I have done
each stage I have typed it on to this page to you to make sure.

 

[Dave Peterson]

My _guess_ is that excel is just guessing that you wanted the contiguous range
(including row 1) sorted when you only selected L2.

Personally, I don't like excel to guess--so I select the range to sort. And I'm
still afraid of those A-Z and Z-A icons. (Yeah, I know that xl2002 added the
warning about expanding the range to include adjacent columns, but I'm chicken.)

Hi Dave,
I have tried again with the same result. I did:
I did select all first and unlocked all cells.
Then highlighted cells B1:Y1 and locked only those cells (header row)
Tools>protection>allow users to edit ranges
Using collapse box typed in B2:Y366 (also previously did it by highlighting
B2:Y366 before going to protection>allow users to edit ranges)
No password
Apply
Protect sheet
Checked boxes are select unlocked cells and sort in the lower portion of
window otherwise all are unchecked. Left the top box of "Protect the
worksheet and contents of locked cells" checked. So in total I have three
boxes on the protect sheet window checked.
Ok
(All columns are unhidden at the beginning of all this process)
Then I click on cell L2, click on A-Z key button and it comes up with "This
cell or chart ... is read only." It says the same thing if I go to Data>Sort
or click the Z-A button.
Is there something somewhere else that I need to go to first or last to tell
it allow sort even though protected. It seems like the sort box checked in
the protection box is not working. But it works okay if I don't lock my
header row. The stages above are exactly what I am doing. As I have done
each stage I have typed it on to this page to you to make sure.

 

[Guest]

Hi Dave,
I did what you suggested and didn't get the usual warning and it worked with
the locked header row. Great I thought, so then I uprotected the sheet and
hid columns B-J and V and W and Z to IU and then protected the sheet. But it
didn't want to sort again but didn't give me the warning message. I then
changed my edit range to A2:Y366 and reprotected the sheet and Wallah!! It
works, so long as I highlight cells A2 to Y366 irrelevant of hidden columns.
It obviously does not like the beginning column of the edit range hidden.
Interestingly enough, even with the range highlighted, I could not sort using
the a-z or Z-A buttons.
Thanks so very, very much Dave.
Sue

 

[Dave Peterson]

Glad you got it working.

Now another thought...

Do you always sort by the same keys?

If yes, maybe you could record a macro when you select the range and do the
sort.

Then use that macro to sort the range. Then you don't have to do the selection
manually.

Hi Dave,
I did what you suggested and didn't get the usual warning and it worked with
the locked header row. Great I thought, so then I uprotected the sheet and
hid columns B-J and V and W and Z to IU and then protected the sheet. But it
didn't want to sort again but didn't give me the warning message. I then
changed my edit range to A2:Y366 and reprotected the sheet and Wallah!! It
works, so long as I highlight cells A2 to Y366 irrelevant of hidden columns.
It obviously does not like the beginning column of the edit range hidden.
Interestingly enough, even with the range highlighted, I could not sort using
the a-z or Z-A buttons.
Thanks so very, very much Dave.
Sue

 

[Guest]

Hi Dave,
Yes, the range is sorted by the same column everytime. That's a brilliant
idea but I have absolutely no idea how to do that at all. How would I go
about it. I'm heading out the door for work now but would love to learn to
do a macro. Would you be able to help.
I will be back in 5 hours if you can leave me a message via this on-line
discussion group I will pick up on it when I get back.
Thanks Dave
Sue

 

[Dave Peterson]

I recorded a macro when I selected the range and sorted it by the first column.

Option Explicit
Sub Macro1()
Application.Goto Reference:="R2C2:R336C25"
Selection.Sort Key1:=Range("B3"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

But I don't like to select my ranges to work with them. (Why disturb the user's
current selection?)

Option Explicit
Sub Macro1A()
Dim myRng As Range
With ActiveSheet
Set myRng = .Range("b2:y336")
With myRng
.Sort Key1:=.Columns(1), Order1:=xlAscending, _
key2:=.Columns(3), order2:=xlDescending, _
key3:=.Columns(8), order3:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End With
End With

End Sub

I also changed the recorded macro to show how you'd include more keys (but your
recorded macro would show that, too. And those .columns(1), .columns(3), and
..columns(8) are the 1st, 3rd and 8th column in that range B:Y--not the 1st, 3rd,
8th column in the worksheet (A, C, H).

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel and test it out via:

tools|macro|macros...
select the macro and click run.

Hi Dave,
Yes, the range is sorted by the same column everytime. That's a brilliant
idea but I have absolutely no idea how to do that at all. How would I go
about it. I'm heading out the door for work now but would love to learn to
do a macro. Would you be able to help.
I will be back in 5 hours if you can leave me a message via this on-line
discussion group I will pick up on it when I get back.
Thanks Dave
Sue

 

[Guest]

Hi Dave,
I'll give it a try. I think I will go to that link and have a good read 1st
before I attempt it.
Thanks heaps for your help and advice, I really appreciate it.
Cheers
Sue

 

[Guest]

Dave and Sue,
I understand about picking the range I want to sort. But the users of the
information I supply use the A-Z, Z-A, and Sort functions. They can sort
using any of the 8 columns of information. And are not spreadsheet savvy to
grab just the data and not the header. And I trust the users less than I
trust Excel keeping the integrity of the data.
Does this protection funtion work at face value that is written in the help
screen?

 

[Dave Peterson]

As far as I know, the help is correct--is there something in it that doesn't
match your experience?

Dave and Sue,
I understand about picking the range I want to sort. But the users of the
information I supply use the A-Z, Z-A, and Sort functions. They can sort
using any of the 8 columns of information. And are not spreadsheet savvy to
grab just the data and not the header. And I trust the users less than I
trust Excel keeping the integrity of the data.
Does this protection funtion work at face value that is written in the help
screen?

 

[Guest]

Dave,
What I read in the Help - if I want a sheet protected so data cannot be
edited in each cell, e.g. 1234 changed to 3412, I make sure each cell is
formatted where the check mark is on "protection" and
tools/protection/protect-worksheet is active.
If I want users to be able to sort these protected cells - then in
tools/protect/protect-worksheet a check is put next to Sort or AutoFilter.
The data in the sheet then will be protected from changes other than sorting.
All the cells in the spreadsheet are protected from data entry or change.
The Sort and AutoFilter boxes are checked in the
tool/protect/protect-worksheet dialogue box. But the data will not sort and
I get the error that protection needs to be removed.
When I read the Help information, all I need to do is check the Sort and
AutoFilter boxes. But that is not the case with this spreadsheet.
I have Excel 2003 SP1.

 

[Dave Peterson]

With a protected sheet, you can only sort an unlocked range--all cells in that
range to sort must be unlocked.

You could always provide a macro to sort the way you want.

Your macro could unprotect the worksheet, sort the range and reprotect the
worksheet.

Dave,
What I read in the Help - if I want a sheet protected so data cannot be
edited in each cell, e.g. 1234 changed to 3412, I make sure each cell is
formatted where the check mark is on "protection" and
tools/protection/protect-worksheet is active.
If I want users to be able to sort these protected cells - then in
tools/protect/protect-worksheet a check is put next to Sort or AutoFilter.
The data in the sheet then will be protected from changes other than sorting.
All the cells in the spreadsheet are protected from data entry or change.
The Sort and AutoFilter boxes are checked in the
tool/protect/protect-worksheet dialogue box. But the data will not sort and
I get the error that protection needs to be removed.
When I read the Help information, all I need to do is check the Sort and
AutoFilter boxes. But that is not the case with this spreadsheet.
I have Excel 2003 SP1.

 

[Guest]

Dave,
According to the Online Help, in Tools/Protect/Protect-sheet, a check can be
put by Sort and AutoFilter. This action will enable both funtions to work
with a proctected worksheet.
To find this information from Help, I typed in "protect and sort worksheet".
From the list of topics, I chose "Enable AutFilter functionality for a
protected worksheet".
According to this information, a person can leave the protection in place
for other data editing when sorting.
Based on what has transpired, this function of Excel does not work.

 

[Dave Peterson]

From xl2003's help for "Elements you can protect in worksheets and workbooks"

Sort When cleared, prevents users from using any of the Sort commands on the
Data menu, or the Sort buttons on the Standard toolbar. Users can't sort ranges
containing locked cells on a protected worksheet, regardless of this setting.

Dave,
According to the Online Help, in Tools/Protect/Protect-sheet, a check can be
put by Sort and AutoFilter. This action will enable both funtions to work
with a proctected worksheet.
To find this information from Help, I typed in "protect and sort worksheet".
From the list of topics, I chose "Enable AutFilter functionality for a
protected worksheet".
According to this information, a person can leave the protection in place
for other data editing when sorting.
Based on what has transpired, this function of Excel does not work.

 

[Guest]

I just ran into this same problem, and chose this solution:

My situation:
The sheet I'm presenting will change in number of rows (i.e. the range is
not constant) but will always need all rows sorted when sorted. I am the only
one changing the data, everyone else just views it in various sorted forms.

My solution:
I unlocked all cells, and when protecting the sheet, deselected "select
unlocked cells" and selected "sort". This way the user can sort the rows as
they need (excel will automatically select all rows to sort when they choose
sort), but they cannot select any of the cells, therefore cannot edit the
values or formulae. There is a header row, but excel lets the user remove the
header row from the sort during the sort dialogue.

This worked for my situation, so I thought I would present it here in case
it helps anyone...

 

[Guest]

I need step by step instructions to protect a part of the excel spreadsheet
there is only one column that I want people to work on the rest I want
protected PLEASE I NEED HELP. I HAVE YAHOO MESSENGER ITS SICASSIDY any
assistance will help