+ Reply to Thread
Results 1 to 11 of 11

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

  1. #1
    Registered User
    Join Date
    11-07-2018
    Location
    Shetland
    MS-Off Ver
    2007
    Posts
    13

    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...

  2. #2
    Forum Contributor
    Join Date
    01-09-2016
    Location
    Perth Western Australia
    MS-Off Ver
    Office 365
    Posts
    257

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

    Hi

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

    Cheers

  3. #3
    Registered User
    Join Date
    11-07-2018
    Location
    Shetland
    MS-Off Ver
    2007
    Posts
    13

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

    Hi FoxSea,

    Thanks for the quick response.

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

  4. #4
    Forum Contributor
    Join Date
    01-09-2016
    Location
    Perth Western Australia
    MS-Off Ver
    Office 365
    Posts
    257

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

    Hi

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

    Cheers

  5. #5
    Registered User
    Join Date
    11-07-2018
    Location
    Shetland
    MS-Off Ver
    2007
    Posts
    13

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

    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

  6. #6
    Forum Contributor
    Join Date
    01-09-2016
    Location
    Perth Western Australia
    MS-Off Ver
    Office 365
    Posts
    257

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

    Thats interesting

  7. #7
    Registered User
    Join Date
    11-07-2018
    Location
    Shetland
    MS-Off Ver
    2007
    Posts
    13

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

    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
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    01-09-2016
    Location
    Perth Western Australia
    MS-Off Ver
    Office 365
    Posts
    257

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

    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

  9. #9
    Registered User
    Join Date
    11-07-2018
    Location
    Shetland
    MS-Off Ver
    2007
    Posts
    13

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

    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.
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    01-09-2016
    Location
    Perth Western Australia
    MS-Off Ver
    Office 365
    Posts
    257

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

    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

  11. #11
    Registered User
    Join Date
    11-07-2018
    Location
    Shetland
    MS-Off Ver
    2007
    Posts
    13

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

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Unable to send user-editable text to Outlook email body based on active cell.
    By m0meehan in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-20-2014, 01:51 PM
  2. Linking cells, need both to remain editable
    By Alannadale in forum Excel General
    Replies: 2
    Last Post: 02-18-2014, 04:17 AM
  3. Tabbing over to editable cells only?
    By Cyberpawz in forum Excel General
    Replies: 3
    Last Post: 05-10-2012, 01:46 PM
  4. Locking a Workbook with editable cells
    By mss00a in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-05-2009, 12:18 PM
  5. Editable locked cells
    By Rfish in forum Excel General
    Replies: 2
    Last Post: 10-19-2007, 12:48 PM
  6. Cell is locked but still editable...
    By nogatomer@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-30-2006, 09:10 AM
  7. Save editable version of Protected file
    By kc in forum Excel General
    Replies: 0
    Last Post: 02-10-2006, 09:50 AM
  8. Excel Editable Ranges
    By Andre in forum Excel General
    Replies: 0
    Last Post: 01-20-2006, 03:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1