+ Reply to Thread
Results 1 to 8 of 8

Checkbox that toggles cells locked/unlocked

  1. #1
    Registered User
    Join Date
    05-15-2019
    Location
    Annapolis, MD
    MS-Off Ver
    10
    Posts
    19

    Checkbox that toggles cells locked/unlocked

    I have a checkbox (Check Box 13) that I want to lock a range of cells and enter the text N/A when it's checked, but be unlocked and able to accept text when the checkbox is unchecked. I have assigned the following macro to a command button (cmdRoll) on the sheet. The checkbox does enter the text N/A when checked and it does lock the range of cells, but if it's unchecked it does not unlock the cells so that text can be entered. I want the specified range of cells to lock when the check box is checked, and the same range to unlock when the checkbox is unchecked. The whole thing only works if the Protect Sheet is on using the pw secret. If I turn off the Protect Sheet, then the code for the range C13:C19,D13:D19 can be altered, which I do not want. I want this code to be in each cell of the range when the checkbox is unchecked:

    =IF(E10=TRUE, "N/A", "")

    But I want the range to be able to fillable with any text if the box is unchecked. If it's checked again, I want the above code to be reinserted.

    The sheet is protected with the pw secret.
    Here is the macro code attached to the command button:
    Please Login or Register  to view this content.
    What code do I need to make the command button macro unlock the cells C13:C19,D13:D19 if the button is unchecked and assign =IF(E10=TRUE, "N/A", "") to the cells if the checkbox is checked, deleting any text that was inserted when the box was unchecked?

    Thank you.
    Last edited by AliGW; 05-20-2019 at 10:35 AM.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,853

    Re: Checkbox that toggles cells locked/unlocked

    I wouldn't use a formula, but use this, which worked for me:

    Please Login or Register  to view this content.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    05-15-2019
    Location
    Annapolis, MD
    MS-Off Ver
    10
    Posts
    19

    Re: Checkbox that toggles cells locked/unlocked

    Thank you, Bernie. This looks like a simpler solution, but it's not working. Do I assign this macro to the command button or to the check box? When I assign to the check box I get an error that says it's the wrong use of the Me command... when I assign it to the button it doesn't seem to do anything at all. I'm very new to macros, so real explicit steps is very helpful.
    Sara

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,872

    Re: Checkbox that toggles cells locked/unlocked

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (Note: this change is not optional. As you are new here, I have done it for you this time.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Registered User
    Join Date
    05-15-2019
    Location
    Annapolis, MD
    MS-Off Ver
    10
    Posts
    19

    Re: Checkbox that toggles cells locked/unlocked

    Thank you, Ali. I see what you did. I will make sure to add those flags if I post again.
    Sara

  6. #6
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,853

    Re: Checkbox that toggles cells locked/unlocked

    I assumed that you are using an Active-X check-box (from the lower section of the Developer / Insert controls menu) based on your code example. Right-click the sheet tab that holds CheckBox13 and select "View Code" and paste the code into the window that appears.

    IF you are using a form control, try changing the macro to this - otherwise, upload a copy of your workbook with the checkbox and cmdRoll objects....

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    05-15-2019
    Location
    Annapolis, MD
    MS-Off Ver
    10
    Posts
    19

    Re: Checkbox that toggles cells locked/unlocked

    That was the problem. I changed my check box to Active-X and it works perfectly now. I so appreciate your help!
    Sara

  8. #8
    Registered User
    Join Date
    05-15-2019
    Location
    Annapolis, MD
    MS-Off Ver
    10
    Posts
    19

    Re: Checkbox that toggles cells locked/unlocked

    I have 58 checkboxes for this project and they all work with the code as above, EXCEPT, that the first checkbox on pages 2-end will only work if the checkbox is located below the 1" mark of the vertical ruler. It doesn't seem to matter if there is one or more rows above it, or if the checkbox gets moved by enlarging a row or by adding rows. The checkboxes are Active-X. I would like to be able to place these boxes closer to the top of each page. What is wrong?
    Thank you,
    Sara

+ 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. [SOLVED] Unlocked cells becoming locked after using activex combo box
    By kfalls in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 08-23-2017, 09:57 AM
  2. [SOLVED] Protecting every sheet and not allowing to select locked or unlocked cells
    By doubl3d80 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-25-2012, 12:42 PM
  3. Highlighting Locked/Unlocked Cells
    By jderren in forum Excel General
    Replies: 2
    Last Post: 10-04-2010, 03:26 PM
  4. When protecting workbook with pw, unlocked cells are still locked
    By Launchnet in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-18-2009, 12:52 AM
  5. Editing unlocked cells in locked form Excel file
    By SeanMclaughlan in forum Excel General
    Replies: 1
    Last Post: 06-25-2009, 05:28 AM
  6. Locked spreadsheet, move cursor only to unlocked cells?
    By dhilligoss in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-05-2005, 10:29 AM
  7. Searching for locked/unlocked cells
    By Karthik Bhat - Bangalore in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-15-2005, 11: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