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.

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.

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

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.

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
>

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

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
>