+ Reply to Thread
Results 1 to 2 of 2

Want only some locked cells selectable

  1. #1
    Registered User
    Join Date
    09-28-2012
    Location
    U.P. Michigan
    MS-Off Ver
    Excel 2007
    Posts
    1

    Want only some locked cells selectable

    I have some worksheets that others have to use. Some cells are unlocked, because the user needs to be able to enter codes or select from a drop-down list. The other cells are locked. For most of the cells containing formulas, I do not want them to be selectable (not really because I don't want users to see the formulas, so much as because I want to constrain selection so that navigating with the arrow keys will guide the user to the next input field).
    However, there are some fields (primarily containing totals of the data in the other locked cells) that need to be selectable (to permit the user to copy/paste the values into other documents) but still locked (to prevent overwriting the formulas).

    Forcing the users to go to a separate worksheet (where all the cells locked but selectable) to find copyable totals is rather inelegant, and something I would like to avoid.

    Thus, in a single protected worksheet, I want there to be a combination of
    Cell type . . . Select? . Edit?
    Input field . . . Y . . . . Y
    Total . . . . . . Y . . . . N
    Calculation . . . N . . . . N


    I realize that the Protect settings apply to the entire worksheet, but is there a way to set permissions at the cell-level?

    I guess I could add a button that runs a macro to toggle the "Select locked cells" permission for the page, but I really prefer not having macros in workbooks I distribute, because it too often seems that, whenever I do include them, there is someone who can't get the sheet to work right -- requiring me to then create new versions with some of the useful features removed. (Well, that, and the security warning messages make the novices worry that something has gone wrong.)

  2. #2
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Want only some locked cells selectable

    Unfortunately - as you already noticed - permissions cannot be set on individual cells.
    You can however simulate that, but would need some vba.
    You could have all the cells protected but selectable and have the selection_change event either unprotect the selected cell or not.
    Cheers!
    Tsjallie




    --------
    If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!

    If you think design is an expensive waste of time, try doing without ...

+ 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. Copy and paste locked cells, and have pasted cells retain LOCKED status.
    By stratloveslenny in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-16-2014, 03:05 PM
  2. Replies: 3
    Last Post: 04-12-2014, 04:47 AM
  3. [SOLVED] Drop Down List Only Selectable If Cells Contain Data
    By swordswinger710 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 06-08-2012, 12:35 PM
  4. Make only one of two cells selectable
    By Greg Jansen in forum Excel General
    Replies: 9
    Last Post: 09-07-2008, 01:43 PM
  5. Put comments on a locked spreadsheet even though cells not locked
    By RDP in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-11-2005, 07:05 PM

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