+ Reply to Thread
Results 1 to 7 of 7

Graying out and Locking Cells Conditionally

  1. #1
    Registered User
    Join Date
    10-26-2015
    Location
    Milwaukee
    MS-Off Ver
    2010/2013
    Posts
    22

    Graying out and Locking Cells Conditionally

    I have a situation where I have 2 questions that I want to ask users of my spreadsheet, the answer is yes or no. I have provided a data validation list box for selection of Yes or No. The default answer for these questions is no.

    What I am looking for is a range of Cells G28:I33 to be grayed out and locked to user input when question 1 is No and have a data validation list box and be unlocked to user input if Question 1 is Yes.

    The same would apply to Question 2 but I assume the formatting would be the same.

    I have attached an example file with desired outcomes and the data validation. In the desired outcome example, I would like the desired outcomes to be there when the cells become active to user input.

    Any help would be appreciated
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Graying out and Locking Cells Conditionally

    Okay, use Conditional Formatting to change the cells Dark Gray if F4 or F5 is No (depending on the cells)
    =$F$4="No"

    Then with the Data Validation, instead of typing your list directly into the DV window, enter it into cells. I used K1:K2 for Yes,No and L1:L3 for Low, Medium, High.
    Then you can use an IF statement in the DV window like so.
    With F6:F10 selected
    Data Validation>List > =IF($F$4="No",$J$1, $K$1:$K$2)
    Do something similar with low, medium, high and with the "Deluxe" workouts.
    Attached Files Attached Files
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    10-26-2015
    Location
    Milwaukee
    MS-Off Ver
    2010/2013
    Posts
    22

    Re: Graying out and Locking Cells Conditionally

    I have gotten the conditional formatting to work but the Data Validation is not this is my formula

    =IF($G$25="No", $Q$1,$O$1:$O$2)

    Same format but different cells it is not changing the cell contents

  4. #4
    Registered User
    Join Date
    10-26-2015
    Location
    Milwaukee
    MS-Off Ver
    2010/2013
    Posts
    22

    Re: Graying out and Locking Cells Conditionally

    What happens is that this statement creates 2 lists a blank list and the Yes/No list, what is does not do is when the parent cell G25 switches from yes to No the original value remains in the cell and does not change to being blank unless manually changed.

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Graying out and Locking Cells Conditionally

    Data Validation will not change what is already in a cell if you change from one list to another. The only way to do that is with VBA. Do you want to add macros to your spreadsheet?

  6. #6
    Registered User
    Join Date
    10-26-2015
    Location
    Milwaukee
    MS-Off Ver
    2010/2013
    Posts
    22

    Re: Graying out and Locking Cells Conditionally

    I have used conditional formatting for when it says no to make the cell content hidden, and to lock hidden cells when I protect the workbook so they cannot even select the cells when they are grayed out. I think I got it all to work the way I wanted it to.

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Graying out and Locking Cells Conditionally

    Sounds good. Glad we could 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. [SOLVED] Help Resolving Runtime Error "28": Out of stack space while conditionally locking cells
    By Seeki in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-29-2015, 11:22 PM
  2. Locking and Graying Cells based on value of another cell
    By tonieasi in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-02-2015, 06:49 AM
  3. [SOLVED] Conditionally Locking A Range
    By kyjae in forum Excel General
    Replies: 3
    Last Post: 03-26-2015, 08:08 PM
  4. Locking specific cells eventually changing to locking all cells
    By davidingilbert in forum Excel General
    Replies: 1
    Last Post: 03-19-2013, 06:50 PM
  5. Help with macro, automatical locking cells when excel is closed but only locking 1 sh
    By snoopy1461 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-21-2011, 11:29 AM
  6. Conditionally locking the cell
    By vickey.narang in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-04-2010, 11:37 AM
  7. Conditionally Locking Cells
    By beesus311 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-25-2008, 01:28 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