Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Aug 2013
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel: Conditional Locking or Data Validation

    I have a cell ($B$1) that I want to restrict, depending on what another cell says.

    Basically, if another cell $A$1 = "False", you can enter whatever you want in this cell. But if $A$1 is "True", then you're prevented from changing what's in $B$1.

    Is there a way to use Data Validation to allow "anything" or "nothing", depending on $A$1?

    Is there a formula that will Lock/Unlock the cell instead?

    I can think of ways to do it in VBA, but I've got a lot of Change events running already.


    -- Shawn

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Allow: Custom
    Formula:
    =NOT(A1)

    Steve

  3. #3
    New Lounger
    Join Date
    Aug 2013
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, that does work.

    There's just one little issue: it doesn't stop the user from clearing (deleting everything from) the cell. Is there a way around that?

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I do not believe there is any way to have data-validation prevent deleting. You will have to use a change macro on the cell B1 or if you are using worksheet protection, you could use a change macro on A1 to lock and unlock the cell.

    Steve

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 646 Times in 590 Posts
    Shawn,

    As Steve said, locking a cell requires the sheet to be protected and that may not be desirable. I know you were not looking for a change event but if you are forced to do so then here is some code with a different approach. If cell A1 = TRUE then the user will not be able to select cell B1 therefore, the contents od B1 will be protected. If A1= FALSE or any other value then B1 can be selected.

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
        If Not Intersect(Target, Range("B1")) Is Nothing Then
            If [a1] = True Then
                Application.EnableEvents = False
                [c1].Select
                Application.EnableEvents = True
            End If
        End If
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2013-10-13 at 05:22.

Tags for this Thread

Posting Permissions

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