lock cellsbased on condition of another cell.

[amazingd]

Does anyone have a way to lock a cell except when there is a specific value in another cell?

 

[Todd Bardoni]

You don't leave much detail, but how about something like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A1") = 1 Then
    ActiveSheet.Unprotect
    Range("B1").Locked = False
Else
    If Cells.Locked = True Then
        Exit Sub
    Else
        Cells.Locked = True
        ActiveSheet.Protect
    End If
End If
End Sub

This goes in the Worksheet module. And, you'll have to change the references based on your needs.

 

[Richie(UK)]

Hi,

Right-click on the sheet tab of your chosen sheet and select View Code. Now paste in the following:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Address <> "$A$1" Then Exit Sub
    'only look for changes in A1
    Me.Unprotect password:="bob"
    Select Case Range("A1").Value
        Case Is = 1: Range("B1").Locked = False
        Case Else: Range("B1").Locked = True
        'if A1=1 then B1 is unlocked, otherwise its locked
        '(set to locked in Workbook_Open if you want to ensure it starts that way)
    End Select
    Me.Protect password:="bob"
End Sub

HTH

PS: Sorry tbardoni - forgot to refresh :wink:

 

[Todd Bardoni]

That's fine because I don't know exactly what the OP wants. I'm sure yours is more of what he needs.

 

[Richie(UK)]

That's fine because I don't know exactly what the OP wants. I'm sure yours is more of what he needs.

:D Well the query wasn't very clear was it? (my response was guesswork too - if the OP wants something different I'm sure he'll repost, hopefully with a little more detail than shown above :wink: )

 

[amazingd]

Richie, Your code worked fine for the cells you used. However, when I changed the cell references, i couldn't get it to work. Also, I was surprised that I could read (and even change) the code with the worksheet protected. This is disturbing, since the password for the worksheet is in the code. (I assume that the same passsword ("bob" in this instance) is used for the code as for the worksheet. Anyway to protect the code/worksheet better?

 

[Richie(UK)]

Hi,

The simplest route here is to apply a password to your VBAProject. Here's how:

1. In the VBE (Alt & F11 from Excel) select your project in the Project window (it should say something like VBAProject(BookName.xls) ).
2. Right click and select VBAProject Properties.
3. Select the Protection tab on the Properties box.
4. Tick 'Lock project for viewing' and enter your chosen password (and confirm).
5. Save your workbook and close it.

The next time you open the workbook it will now only be possible to see the code, and therefore the worksheet password, if the project password is known. (Unless, of course, your users have access to any cracking software to bypass this :wink: ).

The code provided should work fine for whichever cells you choose. In what way would it not work after you made the changes?