Conditional unlock

[johnsail]

Hi
Is it possible to unlock a cell dependent on a specific value being entered
in anther cell in the same row?

 

[Mike H]

Hi,

You can doo it using a change event. This check B1 which must be unlocked
and if the value changes to 999 then A1 becomes unlocked

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("B1")) Is Nothing Then
If Target.Value = 999 Then
ActiveSheet.Unprotect Password:="mypass"
Range("A1").Locked = False
Else
Range("A1").Locked = True
End If
ActiveSheet.Protect Password:="mypass"
End If
End Sub

Mike

 

[johnsail]

Hi Mike
Many thanks for the quick response. The routine "does what it says on the
tin" and I have tried to adapt it to do all that I need it to do.

However I have failed miserably - I have been unable to work out what is
basically going on in your routine.

If you are willing to help further I will spell out exactly what is required:-

1 In each row all cells (A to J) are defined as locked EXCEPT B, C & D.
2 If "X" is entered into C then E should be unlocked to allow fdata entry.
3 If any other data is entered into C then J should be unlocked for data
entry.
4 If C is changed from "X" to "non X" or vice versa then the appropriate
lock / unlock needs to be applied.
5 If the value in C is subsequently deleted then both E and J need to be
locked.

To further complicate things - whenever J is locked it needs to have the
formula inserted that was in the cell to start with. My thoughts on this are
to have a hidden cell (K) that contains a copy of the formula that can be
copied back.

I sincerely hope that you can help.

Regards

John