Conditional Protection (IF then unlock kind of thing..)

[skarp]

No idea whether this is even possible, BUT here's my question:
Can you apply a conditional lock to a cell? I'm envisioning a formula that looks something like this:
IF A1 = "New Hire" then unlock A2, otherwise VLOOKUP..etc..
so A2 would have a different lock/unlock status depending on the value entered in A1.

Of course, there might also be a completely different way of looking at the problem, but I haven't thought of one.. thus my difficulty in searching for solutions.

Thanks for any help!

 

[Nogslaw]

What are your conditions, and your desired results for each condition.

IF A1 = "New Hire" then ???
IF A1 <> "New Hire" then ???

You hinted to a VLOOKUP, but I don't understand the need to lock the cell.

 

[skarp]

IF A1="New Hire", then allow user to enter text in A2
IF A1<>"New Hire", then A2 performs a VLOOKUP function and populates A2. (the lookup functions works fine)

The sheet will be protected and cells with formulas will generally be locked. But A2 falls in this category where it should be locked if A1<>"new Hire" but unlocked if A1="New Hire". Is that possible?

 

[Nogslaw]

This can be done through macros.

What will your VLOOKUP formula be?
Will this only be for A1 or multiple cells?

 

[skarp]

shoot, i don't know how to write macros, so i wouldn't be able to maintain/fix it.. thanks for your answer though!

 

[Nogslaw]

Until a couple of years ago, I didn't either. I'm still learning, but they are not as difficult as people sometimes think. And if you need help in the future, we are here for you!

Anyway, here is the code and instructions to install it if you want to check it out.

Dim disabled As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
'ONLY RUN IF A SINGLE CELL IS SELECTED, CANCEL IF MULTIPLE CELLS SELECTED
If Target.Count > 1 Then Exit Sub

'ONLY RUN ONCE (PREVENTS CONTINUOUS LOOPS)
If disabled Then Exit Sub

'ONLY RUN IF A1 IS THE CELL THAT IS CHANGED
If Target.Address = "$A$1" Then
    disabled = True
    
    'IF ENTRY IS "NEW HIRE"
    If Target.Value = "New Hire" Then
        'UNLOCK CELL A2
        With Sheets("Sheet1")
            .Unprotect
            .Range("A2").ClearContents
            .Range("A2").Locked = False
            .Protect
        End With
        disabled = False
        Exit Sub
    End If
    
    'IF ENTRY IS ANYTHING EXCEPT "NEW HIRE"
    'LOCK CELL A2 AND INSERT VLOOKUP FORMULA
        With Sheets("Sheet1")
            .Unprotect
            .Range("A2").Formula = "=VLOOKUP(A1, YOUR LOOKUP RANGE, COLUMN INDEX, TRUE/FALSE)"  'YOU WILL NEED TO EDIT THIS FORMULA
            .Range("A2").Locked = True
            .Protect
        End With
End If

disabled = False
End Sub

To Install:

Right Click on the Sheet Tab and select View Code
Copy/Paste the above code
Edit the line towards the end of the code that has the VLOOKUP formula so that the formula matches what you need it to say.