+ Reply to Thread
Results 1 to 11 of 11

Highlighting cells with check box while sheet is protected

  1. #1
    Registered User
    Join Date
    07-03-2017
    Location
    SC
    MS-Off Ver
    2016
    Posts
    27

    Highlighting cells with check box while sheet is protected

    Hello,

    I want the following code to run when a box is checked:
    Please Login or Register  to view this content.
    This blacks out a portion of cells.

    However, when I protect the sheet I get the following error message when I try and click the (unlocked) check box:"The cell or chart you are trying to change is on a protected sheet..."

    I attempted to allow formatting when protecting the sheet but this does not work. Any help is appreciated. Since there are many check boxes the code above is repeated many times with different areas of the sheet being blacked out.

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Highlighting cells with check box while sheet is protected

    The code has to unprotect the sheet at the beginning and then re-protect it at the end. Change the sheet name to suit your needs.
    Please Login or Register  to view this content.
    This assumes the sheet is not password protected. If you used a password, it must be included in the code :
    Please Login or Register  to view this content.
    Last edited by Mumps1; 08-10-2017 at 01:18 PM.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Registered User
    Join Date
    07-03-2017
    Location
    SC
    MS-Off Ver
    2016
    Posts
    27

    Re: Highlighting cells with check box while sheet is protected

    This doesn't work, and I still get the same error, my guess is because the unprotect sheet would have to come before the click event?

  4. #4
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    Re: Highlighting cells with check box while sheet is protected

    Does the code even execute? Put a Stop or breakpoint to confirm. Perhaps the control name does not match the code. This is just a rudimentary "check off step"; otherwise I'm surprised. No, the .Unprotect would not be performed before clicking; that was a good thought, but logically, there's no where before the Click code that you would do that (Click is the first event to run when you click).

  5. #5
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    Re: Highlighting cells with check box while sheet is protected

    I have to correct myself, there are events that might precede _click - some that jump to mind are lost focus, BeforeDoubleClick et al, events upon leaving another control, etc. However the Click code is where you would unprotect.
    Chip, if you can read this, you're STILL making the world a better place with your immeasurably valuable gifts. Every week, you continue to help me (and untold numbers of others) tremendously. Practically no alternative outlets can match your effectiveness, even over the vast number of topics you demystify and empower usage of. You were, and still are, amazing.

  6. #6
    Registered User
    Join Date
    07-03-2017
    Location
    SC
    MS-Off Ver
    2016
    Posts
    27

    Re: Highlighting cells with check box while sheet is protected

    It does execute when the sheet is unlocked....but unfortunately that same message appears if the sheet is protected (then does not execute).

  7. #7
    Registered User
    Join Date
    07-03-2017
    Location
    SC
    MS-Off Ver
    2016
    Posts
    27

    Re: Highlighting cells with check box while sheet is protected

    I can get rid of the VBA code and just use conditional formatting but I still get that error message for the conditional formatting too. Is there a code to allow conditional formatting when the sheet is protected? Note- I have tried to simply click the "allow users to format cells" button when locking the sheet, unfortunately that didn't help.

  8. #8
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    Re: Highlighting cells with check box while sheet is protected

    Forget all I said. I was so off base. See if there is a linked cell (check properties on the checkbox, in design mode, while unprotected). If so, that cell needs to be unlocked.

    EDIT: IGNORE THIS, and read next post -->> To do that, if that's what is afflicting you, right after the unprotect,
    range("E3").locked=false
    or much better, name your range appropriately, and
    range("rngBababouie").locked=false

    Then relock on the way out of the Click routine, before re-protecting the sheet. <<-- IGNORE
    Last edited by Oppressed1; 08-10-2017 at 11:06 PM. Reason: See "EDIT" in edited post

  9. #9
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    Re: Highlighting cells with check box while sheet is protected

    Someone more experienced with controls and linked cells should confirm this, but I'm beginning to think that the Linked cell needs to be always left unlocked, and then you just rely on the sheet protection to keep meddling fingers off of it. I confess that I can't positively say that.

    Sorry, I'm much more rusty on this than I thought when first answering, not having done that for many years. I have gone back and looked at some and I have found that the linked cell was kept permanently unlocked in each case. Whether or not it's a requirement, it does work to prevent the message, while still keeping the user from modifying the linked cell (when the sheet is protected).

    Generally speaking, when you protect a sheet, every cell becomes locked, unless you individually unlocked, either through the format interface (generally easier for doing the one-time-only unlocking) or by code. Of course, using the Format interface, you can select a range first to do multiple cells.
    Last edited by Oppressed1; 08-10-2017 at 11:13 PM. Reason: reworded sentence to add " permanently"

  10. #10
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    Re: Highlighting cells with check box while sheet is protected

    Looking again at your code, I'm pretty sure you just need to unlock cell F18, and leave it unlocked, and your issue will disappear - with conditional formatting as well. Sorry to ramble so long to get to that simple answer.

  11. #11
    Registered User
    Join Date
    07-03-2017
    Location
    SC
    MS-Off Ver
    2016
    Posts
    27

    Re: Highlighting cells with check box while sheet is protected

    haha, you rock, F18 was the culprit! Thank you so much for your help!

+ 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. Cannot use VBA check box with protected sheet/columns
    By wernertess in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-09-2014, 05:01 AM
  2. Replies: 2
    Last Post: 02-06-2013, 12:13 AM
  3. Replies: 0
    Last Post: 02-05-2013, 07:51 AM
  4. [SOLVED] Highlighting Protected/Unprotected Cells
    By ahanmagey in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-03-2008, 11:38 AM
  5. Highlighting Protected/Unprotected Cells
    By ChemistB in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-01-2008, 02:28 PM
  6. Enable check box in protected sheet + group check boxes
    By Dexxterr in forum Excel General
    Replies: 4
    Last Post: 08-02-2006, 07:05 AM
  7. [SOLVED] Allow unprotected cells to spell check after sheet is protected
    By Numberonekraut (Hans) in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-15-2005, 03:05 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