Sorting a locked table on a protected sheet with VBA

Hi all,

I have trawled through many threads trying to find a solution to my problem with no luck. There seems to be a lot of people with the same problem.
I am using excel 2011.

How can I sort a table on a protected sheet?

1. I navigate to Tools > Protection > Protect Sheet...
2. I select the boxes for (Select locked cells, Select unlocked cells, Sort, Filter)
3. I try to sort by ascending or descending to be faced with "The cell or chart you are trying to change is protected and therefore read-only."
4. I notice that I can still filter without any problems.
5. This happens whether the cells that I am trying to sort are locked or not.

I am baffled by this and if need be I am open to VBA.

It is necessary to have the sheet protected, the cells locked and the sort function working.

Thank you for your help

Hi Freddobonanza,

Just checked, I am able to sort the cells after unlocking them - provided the sort option is checked when protected the sheet.

Regards,
DILIPandey

<click on below 'star' if this helps>

@dilipandey thanks for your reply; yes I can do this sorry.. that was a bit of a typo in #5.
Like I said though

It is necessary to have the sheet protected, the cells locked and the sort function working.

Which is not working.
Thank you for your help

You need to set this up in the Options available when protecting, i.e. allow sort. Then protect the sheet & the user will be able to sort.

The cells must also be unlocked in the table

@royUK haven't I covered this with

1. I navigate to Tools > Protection > Protect Sheet...
2. I select the boxes for (Select locked cells, Select unlocked cells, Sort, Filter)
3. I try to sort by ascending or descending to be faced with "The cell or chart you are trying to change is protected and therefore read-only."
4. I notice that I can still filter without any problems.

Thank you for your help

Well you are doing something wrong, probably leaving the cells locked. feel free to carry on I'm finished with your question!!!

Okay thanks for your help royUK. I'm not ready to admit I'm doing anything wrong until my question has been answered. Like I said it is necessary to have the cells locked.

trouble withsort.xlsx in this sheet, the cells are all locked to stop people fiddling and the sheet is protected with the options of selecting locked, unlocked cells, sorting and filtering enabled. (the sheet is protected without a password)

Can someone please try this example sheet?

Thank you for your help

Hi Freddobonanza..

I guess you are confused with the words Lock and Protect...

I just managed to solve this.. see the attachment and you'll find that you can do the sort

Regards,
DILIPandey

<click on below 'star' if this helps>

That's what i suggested!

protectsheet.jpg
These are the options I am ticking. When I select these options, I can still delete data out of the table. I would like to be able to select the data in the table for copying only.

What options were you ticking dilipandey?
I can't select any data on the table and when I say this I mean just for copying purposes not editing as I don't want anyone playing with hidden formulas.

There are other cells on the same sheet (input cells), which I want to be able to edit. Thus keeping those cells unlocked under Format cells.

I hope I have explained this clearly enough..?

Hi Freddobonanza..

For my learning, just wanted to see one thing:-
On your image, can you have just one option checked "Select Locked Cells" along with Sort option checked and Autofilter option checked. so total 3 checked out of 4 checked you have shown.
If you are able to do it, please share the screen shot image. Thanks.

I am assuming that :-
Assumption:- the cells in consideration are Not Locked i.e., Format - Protection tab -> Locked option is unchecked.

Regards,
DILIPandey

<click on below 'star' if this helps>

Hi dilipandey,

Thank you for your help, it is much appreciated.
Okay, I have attached the worksheet I am working on. (stripped to the bones)
workbook.xlsx

There are light blue cells on the sheet that I need to keep unlocked for editing, so I need the option ticked when protecting: Select unlocked cells.
The cells in the table are 'unlocked' under 'format cells'.
The sheet has been protected with the 3 options: Select unlocked cells, Sort and Filter.
* The result is I can sought the table, but I can edit the cells in the table which isn't what I want to do.
I would like to be able to select the cells in the table only for copying not editing.

I have tried 'locking' the cells in the table under 'format cells'.
* The result is a table that I cannot sort once the sheet is protected.

Like I have said it is necessary to be able to select the data of the table for copying but not editing. I would have thought that I would have to "lock" the cells under 'format cells' and select the 4 options when protecting: Select locked cells, Select unlocked cells, Sort and Filter.

Thank you once again for you help

Hi,

On your image, can you have just one option checked "Select Locked Cells" along with Sort option checked and Autofilter option checked. so total 3 checked out of 4 checked you have shown.

can you try above and share the outcome?

You have said in your post that

The sheet has been protected with the 3 options: Select unlocked cells, Sort and Filter.

which is not the exact options I had mentioned.

Regards,
DILIPandey


<click on below 'star' if this helps>

I don't understand what you are asking because.. You are saying..

can you have just one option checked "Select Locked Cells" along with Sort option checked and Autofilter option checked. so total 3 checked out of 4 checked you have shown.

I can't check the option "Select Locked Cells" without excel automatically checking the option "Select Unlocked Cells" at the same time.
In other words, if you want the "Select locked Cells" checked you have to have both, therefore when you select the "Sort" and "Filter" option it comes to 4 options selected.
I understand that the options I have selected aren't the exact options as you mentioned as I need to be able to select unlocked cells so I can edit other cells on the same sheet.
I hope this clears things up a bit.

It seems to be that you can't sort a table with locked cells on a protected sheet? As royUK wrote

The cells must also be unlocked in the table

Do I need to use VBA to do this?

Thank you for your help

Maybe the title of this thread needs to be changed to "Sorting a locked table on a protected sheet"

Yes.. Freddobonanza.. I am in complete agreement about the title which you have suggested

What I am trying to do here is -> to drive you to the solution.. which you are now arrived to.. as it seems to me.

As you can't have checked only "Select locked cells" in this case,so you are:-

-> not able to select them
-> since you are not able to select them, you can't copy them
-> since you are not able to select them, you can't edit them

you said

Like I have said it is necessary to be able to select the data of the table for copying but not editing

So now.. you can enable to user to sort the cells by this arrangement but you can't let them copy the cells because if you enable them to copy cells, this will mean that they should have the right to select (which is restricted here as they can't EVEN SELECT these cells).. so it is a kind of restriction from our dear Excel's side.

I learned few things here and hope you also

Now, the solution -> VBA (Macros).. but how will the macro judge that if a user is just want to select them or copy them and do not want to edit them ?

Regards,
DILIPandey

<click on below 'star' if this helps>

Hi dilipandey,
Finally we have an understanding of my problem

This is really frustrating that Excel has this restriction or limitation.
Surely other people have bridged this problem?
Does anyone have any ideas or VBA coding they are willing to share?
At the moment my workbook is left open to anyone to fiddle with, therefore I cannot share it

Thank you for your help

farmedgirl,

Hello DILIPandey
I realize this thread is quite old,

Welcome to the Forum.

1. Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

2. Your post does not comply with Rule 12 of our Forum RULES. Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.