Locking Cells
 

Hi,

Is there a way to lock a cell depending on the entry on another?

e.g, If A1="yes" then B2 would become locked and the user would not be able
you make an entry.

Thanks

SPL

Locking Cells
 

One way

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "A1"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Unprotect
.Offset(1, 1).Locked = .Value = "Yes"
Me.Protect
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"PH NEWS" wrote in message
...
Hi,

Is there a way to lock a cell depending on the entry on another?

e.g, If A1="yes" then B2 would become locked and the user would not be
able
you make an entry.

Thanks

SPL

Locking Cells
 

Thanks, but that just seems to protect the whole sheet. I would like to be
able to pick just one cell to protect depending on the entry in another?
"Bob Phillips" wrote in message
...
One way

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "A1"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Unprotect
.Offset(1, 1).Locked = .Value = "Yes"
Me.Protect
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"PH NEWS" wrote in message
...
Hi,

Is there a way to lock a cell depending on the entry on another?

e.g, If A1="yes" then B2 would become locked and the user would not be
able
you make an entry.

Thanks

SPL

Locking Cells
 

What you need to do is to unlock all cells first, select the whole sheet,
goto FormatCellsProtection and uncheck the locked box.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"PH NEWS" wrote in message
...
Thanks, but that just seems to protect the whole sheet. I would like to be
able to pick just one cell to protect depending on the entry in another?
"Bob Phillips" wrote in message
...
One way

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "A1"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Unprotect
.Offset(1, 1).Locked = .Value = "Yes"
Me.Protect
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"PH NEWS" wrote in message
...
Hi,

Is there a way to lock a cell depending on the entry on another?

e.g, If A1="yes" then B2 would become locked and the user would not be
able
you make an entry.

Thanks

SPL