Cells editable even though locked & protected? User-editable ranges

Hi everyone,

I'm new here so please be gentle

I am using Excel 2007 and have run into the problem discussed in this post: excelforum DOT com/excel-general/759683-still-able-to-change-cells-that-are-locked-and-protected.html

(I can't post links yet, but I don't want to copy the content of the whole thread)

Namely, that I am still able to edit cells despite them being locked and protected.

In that discussion it is suggested that the cause is related to user-editable ranges and it is proposed that clicking "Allow Users to Edit Ranges" and deleting any ranges there will resolve the issue. However, my data is in a table and I need these ranges in order to allow sorting and filtering of the table.

So, is it possible to have cells actually protected AND have the user-editable ranges that allow for sorting/filtering?

Lastly, I know I can uncheck "Select locked cells", but this brings with it other problems...

Hi

Have you locked the cells then protected the sheet then saved and re-opened the file.

Cheers

Hi FoxSea,

Thanks for the quick response.

I have just tried your suggestion, but unfortunately to no effect.

Hi

Can you try uploading a sample of the spreadsheet now by going to 'Go Advanced' Manage Attachments select and upload.

Cheers

Hi,

I've cleared the data and I'll see if I can prepare something to upload.

Incidentally, when clearing data just now I had "Select locked cells" unchecked and, appropriately, I couldn't select locked cells, not individually anyway. However, if I selected multiple cells beginning with an unlocked cell, I could select any cells and edit/delete their contents.

Cheers

Thats interesting

Hi again - sorry for the radio silence. I was waiting for permission to upload a copy of the file I was working with. It never came, so I have prepared a test workbook following the same steps. It seems to replicate the problematic behaviour.

So, the steps I took were:

1. Create table
   
2. For all the cells containing "Unprotected cell", Format Cells > Protection > unchecked 'Locked'
   
3. In 'Allow users to edit ranges' > New > created range TestTableRange referring to the whole table ($A$1:$M$25)
   
4. Protect Sheet > check the following:
   

• Select locked cells
  
• Select unlocked cells
  
• Sort
  
• Use Autofilter

With the above checked, I can select locked cells and edit/delete contents.

With 'Select locked cells' unchecked, I can still delete the contents of locked cells if I select a range starting in an unlocked cell (as per my last post).

As per my original post, I am using Excel 2007. I have used this method in Excel 2010 and haven't run into the same issues.

Please let me know if you need any more information.

Many thanks

Hi

Try setting several 'Allow users to edit ranges - one for each of the columns where user can edit ie excluding the protected cells. I cannot see why there should be an issue and do not remember having the problem when using 2007.
Just remember to protect the sheet and save then reopen the file.
Cheers

Hi,

Thanks for your reply. I have tried what you suggest and, when the sheet is protected, it does prevent editing/deleting contents of protected cells, and it allows for filtering on both the protected and unprotected columns, but it doesn't allow for sorting in any columns.

I have attached the revised table, however I have just found a Stack Overflow thread about these issues and it seems like I'm on a hiding to nothing. I can't add a link, but the discussion is number 10197772 and WoodenKitty summarises the main issues. There are VBA solutions, which I may have to consider...

Thanks for taking the time to look into this.

Hi
Once you unprotect the sheet then you should be able to sort. This would however restrict the sorting to an administrator ie with a password. and leave then sheet vulnerable whilst unprotected. Developing VBA to enable sorting will become problematic on restrictions required ie who can (password again) which columns etc.
Cheers

Hi,

Thanks for all your help on this. It seems I can't have the flexibility AND the robustness that I wanted so I'll look at other ways of structuring things.

Cheers