conditional unlock

[johnsail]

Is there a way to unlock a cell dependant on values entered in another
(unlocked) cell?

Excell 2003

 

[Mike H]

Hi,

Right click the sheet tab, view code and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1").Value = "lock" Then
ActiveSheet.Unprotect
Range("B1").Locked = True
ActiveSheet.Protect

Else
ActiveSheet.Unprotect
Range("B1").Locked = False
ActiveSheet.Protect
End If
End Sub

Mike

 

[johnsail]

Hi Mike,
First thanks for quick response.

Am new to macros / VB so don't really understand how to use your info. Also
don't know if my question was clear so . . . .

3 cells in worksheet. A1 B1 C1.
A1 unlocked. B1 and C1 both locked.

Enter cat in A1 and need to unlock B1 so data can be entered.
Enter dog in A1 and need to unlock C1 so data can be entered.

John

 

[Gord Dibben]

Assuming you have A1:A10 unlocked and all other cells locked this event code
will unlock adjacent cells in column B.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Me.Range("A1:A10")) Is Nothing Then Exit Sub
On Error GoTo enditall
Application.EnableEvents = False
If Target.Value <> "" Then
ActiveSheet.Unprotect Password:="justme"
With Target.Offset(0, 1)
.Locked = False
End With
End If
enditall:
Application.EnableEvents = True
ActiveSheet.Protect Password:="justme"
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste into that sheet module.


Gord Dibben MS Excel MVP