If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sorting on a protected worksheet
I have a workbook that has some cells locked/hidden and the worksheets
protected, because I don't want my formula's messed with. However there are other cells where a user (not me) will input data and need to sort it into alpha or date of birth order, but they can't due to the sheet being protected. The unlocked cells box has been checked of course in the tools/protection window before passwording and I have checked the sorting box as well so... why won't it let it be sorted. It comes up with sheet is protected, read only, need to remove protection to get it to sort. But if I remove the protection I run the risk of someone deleting my formula's. Can anyone help? Why is there a selection of boxes to check what you want to allow to have happen once protected, if you can't do it. I know the unlocked cells one works once the sheet is protected so why not sort? What am I doing wrong? The only thing I do is the template, no data imputing and due to the user's level of competency it is too risky to leave formula's exposed. It is not a shared workbook by the way. |
#2
|
|||
|
|||
By the way it is excel 2003 I'm working with.
"Sue" wrote: I have a workbook that has some cells locked/hidden and the worksheets protected, because I don't want my formula's messed with. However there are other cells where a user (not me) will input data and need to sort it into alpha or date of birth order, but they can't due to the sheet being protected. The unlocked cells box has been checked of course in the tools/protection window before passwording and I have checked the sorting box as well so... why won't it let it be sorted. It comes up with sheet is protected, read only, need to remove protection to get it to sort. But if I remove the protection I run the risk of someone deleting my formula's. Can anyone help? Why is there a selection of boxes to check what you want to allow to have happen once protected, if you can't do it. I know the unlocked cells one works once the sheet is protected so why not sort? What am I doing wrong? The only thing I do is the template, no data imputing and due to the user's level of competency it is too risky to leave formula's exposed. It is not a shared workbook by the way. |
#3
|
|||
|
|||
If you're careful and only select the cells to sort--and all those cells in the
selection are unlocked, then you should be able to sort the data (with allow users to sort checked). But my bet is you want to sort some cells that are locked on that protected sheet. Maybe you could provide a macro (or two) that would unprotect the worksheet, sort the data, and then reprotect the worksheet. If you record a macro when you do it and need help modifying it, post back. Sue wrote: I have a workbook that has some cells locked/hidden and the worksheets protected, because I don't want my formula's messed with. However there are other cells where a user (not me) will input data and need to sort it into alpha or date of birth order, but they can't due to the sheet being protected. The unlocked cells box has been checked of course in the tools/protection window before passwording and I have checked the sorting box as well so... why won't it let it be sorted. It comes up with sheet is protected, read only, need to remove protection to get it to sort. But if I remove the protection I run the risk of someone deleting my formula's. Can anyone help? Why is there a selection of boxes to check what you want to allow to have happen once protected, if you can't do it. I know the unlocked cells one works once the sheet is protected so why not sort? What am I doing wrong? The only thing I do is the template, no data imputing and due to the user's level of competency it is too risky to leave formula's exposed. It is not a shared workbook by the way. -- Dave Peterson |
#4
|
|||
|
|||
I am currently using XL2000 and would have solved your problem with a macro
that unprotects the sheet, then do the sorting and finally protects the sheet again. -- Regards, Martin "Sue" wrote: By the way it is excel 2003 I'm working with. "Sue" wrote: I have a workbook that has some cells locked/hidden and the worksheets protected, because I don't want my formula's messed with. However there are other cells where a user (not me) will input data and need to sort it into alpha or date of birth order, but they can't due to the sheet being protected. The unlocked cells box has been checked of course in the tools/protection window before passwording and I have checked the sorting box as well so... why won't it let it be sorted. It comes up with sheet is protected, read only, need to remove protection to get it to sort. But if I remove the protection I run the risk of someone deleting my formula's. Can anyone help? Why is there a selection of boxes to check what you want to allow to have happen once protected, if you can't do it. I know the unlocked cells one works once the sheet is protected so why not sort? What am I doing wrong? The only thing I do is the template, no data imputing and due to the user's level of competency it is too risky to leave formula's exposed. It is not a shared workbook by the way. |
#5
|
|||
|
|||
Hi,
Thanks for the advise. I wouldn't know where to start to do a macro. I'm just an innocent??**!! I have double checked that all the cells where the info. lays across all the columns is definitely unlocked and when I protect the sheet I have double checked that the sorted box is checked. It still won't let me sortonce I protect the sheet. It says that while it is protected it is read only. Any other ideas? "Dave Peterson" wrote: If you're careful and only select the cells to sort--and all those cells in the selection are unlocked, then you should be able to sort the data (with allow users to sort checked). But my bet is you want to sort some cells that are locked on that protected sheet. Maybe you could provide a macro (or two) that would unprotect the worksheet, sort the data, and then reprotect the worksheet. If you record a macro when you do it and need help modifying it, post back. Sue wrote: I have a workbook that has some cells locked/hidden and the worksheets protected, because I don't want my formula's messed with. However there are other cells where a user (not me) will input data and need to sort it into alpha or date of birth order, but they can't due to the sheet being protected. The unlocked cells box has been checked of course in the tools/protection window before passwording and I have checked the sorting box as well so... why won't it let it be sorted. It comes up with sheet is protected, read only, need to remove protection to get it to sort. But if I remove the protection I run the risk of someone deleting my formula's. Can anyone help? Why is there a selection of boxes to check what you want to allow to have happen once protected, if you can't do it. I know the unlocked cells one works once the sheet is protected so why not sort? What am I doing wrong? The only thing I do is the template, no data imputing and due to the user's level of competency it is too risky to leave formula's exposed. It is not a shared workbook by the way. -- Dave Peterson |
#6
|
|||
|
|||
I'd double check that range for locked cells once more.
Unprotect the sheet. select the range format|cells|protection tab If you see that the locked checkbox is empty, then you're correct (and I don't have another guess). If you see that it's checked, then all cells in the selection are locked. If it shows a grey check (or a green blob in xl2003), then there is a mixture of locked and unlocked cells in that selection. == A nice way to start with macros is tools|macro|record new macro. Sue wrote: Hi, Thanks for the advise. I wouldn't know where to start to do a macro. I'm just an innocent??**!! I have double checked that all the cells where the info. lays across all the columns is definitely unlocked and when I protect the sheet I have double checked that the sorted box is checked. It still won't let me sortonce I protect the sheet. It says that while it is protected it is read only. Any other ideas? "Dave Peterson" wrote: If you're careful and only select the cells to sort--and all those cells in the selection are unlocked, then you should be able to sort the data (with allow users to sort checked). But my bet is you want to sort some cells that are locked on that protected sheet. Maybe you could provide a macro (or two) that would unprotect the worksheet, sort the data, and then reprotect the worksheet. If you record a macro when you do it and need help modifying it, post back. Sue wrote: I have a workbook that has some cells locked/hidden and the worksheets protected, because I don't want my formula's messed with. However there are other cells where a user (not me) will input data and need to sort it into alpha or date of birth order, but they can't due to the sheet being protected. The unlocked cells box has been checked of course in the tools/protection window before passwording and I have checked the sorting box as well so... why won't it let it be sorted. It comes up with sheet is protected, read only, need to remove protection to get it to sort. But if I remove the protection I run the risk of someone deleting my formula's. Can anyone help? Why is there a selection of boxes to check what you want to allow to have happen once protected, if you can't do it. I know the unlocked cells one works once the sheet is protected so why not sort? What am I doing wrong? The only thing I do is the template, no data imputing and due to the user's level of competency it is too risky to leave formula's exposed. It is not a shared workbook by the way. -- Dave Peterson -- Dave Peterson |
#7
|
|||
|
|||
Hi Dave,
Thanks for the advise. I have again double checked the cells. They are definitely not locked within the range in any way. In fact the only cells that are locked is the header row as it has a formula built into a couple of the header columns, otherwise the rest are unlocked. Am I frustrated!! You bet. Can anyone help? "Dave Peterson" wrote: I'd double check that range for locked cells once more. Unprotect the sheet. select the range format|cells|protection tab If you see that the locked checkbox is empty, then you're correct (and I don't have another guess). If you see that it's checked, then all cells in the selection are locked. If it shows a grey check (or a green blob in xl2003), then there is a mixture of locked and unlocked cells in that selection. == A nice way to start with macros is tools|macro|record new macro. Sue wrote: Hi, Thanks for the advise. I wouldn't know where to start to do a macro. I'm just an innocent??**!! I have double checked that all the cells where the info. lays across all the columns is definitely unlocked and when I protect the sheet I have double checked that the sorted box is checked. It still won't let me sortonce I protect the sheet. It says that while it is protected it is read only. Any other ideas? "Dave Peterson" wrote: If you're careful and only select the cells to sort--and all those cells in the selection are unlocked, then you should be able to sort the data (with allow users to sort checked). But my bet is you want to sort some cells that are locked on that protected sheet. Maybe you could provide a macro (or two) that would unprotect the worksheet, sort the data, and then reprotect the worksheet. If you record a macro when you do it and need help modifying it, post back. Sue wrote: I have a workbook that has some cells locked/hidden and the worksheets protected, because I don't want my formula's messed with. However there are other cells where a user (not me) will input data and need to sort it into alpha or date of birth order, but they can't due to the sheet being protected. The unlocked cells box has been checked of course in the tools/protection window before passwording and I have checked the sorting box as well so... why won't it let it be sorted. It comes up with sheet is protected, read only, need to remove protection to get it to sort. But if I remove the protection I run the risk of someone deleting my formula's. Can anyone help? Why is there a selection of boxes to check what you want to allow to have happen once protected, if you can't do it. I know the unlocked cells one works once the sheet is protected so why not sort? What am I doing wrong? The only thing I do is the template, no data imputing and due to the user's level of competency it is too risky to leave formula's exposed. It is not a shared workbook by the way. -- Dave Peterson -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Expand/Collapse Grouped Data in Protected Worksheet | Robert Slattery | General Discussion | 6 | May 13th, 2010 07:44 AM |
How to Sort Protected Worksheet | mr.lord | Worksheet Functions | 1 | May 28th, 2005 07:48 AM |
Import/Export XML in protected worksheet | jgentillet | General Discussion | 0 | May 13th, 2005 05:42 PM |
How do I show protected cells in an EXCEL worksheet | laurentdada | General Discussion | 3 | April 19th, 2005 01:45 PM |