software

Hi,

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

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

Thanks

SPL
One way

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = quot;A1quot;

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 = quot;Yesquot;
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)

quot;PH NEWSquot; gt; wrote in message
...
gt; Hi,
gt;
gt; Is there a way to lock a cell depending on the entry on another?
gt;
gt; e.g, If A1=quot;yesquot; then B2 would become locked and the user would not be
able
gt; you make an entry.
gt;
gt; Thanks
gt;
gt; SPL
gt;
gt;
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?
quot;Bob Phillipsquot; gt; wrote in message
...
gt; One way
gt;
gt; '-----------------------------------------------------------------
gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; '-----------------------------------------------------------------
gt; Const WS_RANGE As String = quot;A1quot;
gt;
gt; On Error GoTo ws_exit:
gt; Application.EnableEvents = False
gt; If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
gt; With Target
gt; Me.Unprotect
gt; .Offset(1, 1).Locked = .Value = quot;Yesquot;
gt; Me.Protect
gt; End With
gt; End If
gt;
gt; ws_exit:
gt; Application.EnableEvents = True
gt; End Sub
gt;
gt; 'This is worksheet event code, which means that it needs to be
gt; 'placed in the appropriate worksheet code module, not a standard
gt; 'code module. To do this, right-click on the sheet tab, select
gt; 'the View Code option from the menu, and paste the code in.
gt;
gt;
gt;
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;PH NEWSquot; gt; wrote in message
gt; ...
gt; gt; Hi,
gt; gt;
gt; gt; Is there a way to lock a cell depending on the entry on another?
gt; gt;
gt; gt; e.g, If A1=quot;yesquot; then B2 would become locked and the user would not be
gt; able
gt; gt; you make an entry.
gt; gt;
gt; gt; Thanks
gt; gt;
gt; gt; SPL
gt; gt;
gt; gt;
gt;
gt;
What you need to do is to unlock all cells first, select the whole sheet,
goto Formatgt;Cellsgt;Protection and uncheck the locked box.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;PH NEWSquot; gt; wrote in message
...
gt; Thanks, but that just seems to protect the whole sheet. I would like to be
gt; able to pick just one cell to protect depending on the entry in another?
gt; quot;Bob Phillipsquot; gt; wrote in message
gt; ...
gt; gt; One way
gt; gt;
gt; gt; '-----------------------------------------------------------------
gt; gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt; '-----------------------------------------------------------------
gt; gt; Const WS_RANGE As String = quot;A1quot;
gt; gt;
gt; gt; On Error GoTo ws_exit:
gt; gt; Application.EnableEvents = False
gt; gt; If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
gt; gt; With Target
gt; gt; Me.Unprotect
gt; gt; .Offset(1, 1).Locked = .Value = quot;Yesquot;
gt; gt; Me.Protect
gt; gt; End With
gt; gt; End If
gt; gt;
gt; gt; ws_exit:
gt; gt; Application.EnableEvents = True
gt; gt; End Sub
gt; gt;
gt; gt; 'This is worksheet event code, which means that it needs to be
gt; gt; 'placed in the appropriate worksheet code module, not a standard
gt; gt; 'code module. To do this, right-click on the sheet tab, select
gt; gt; 'the View Code option from the menu, and paste the code in.
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;PH NEWSquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Hi,
gt; gt; gt;
gt; gt; gt; Is there a way to lock a cell depending on the entry on another?
gt; gt; gt;
gt; gt; gt; e.g, If A1=quot;yesquot; then B2 would become locked and the user would not be
gt; gt; able
gt; gt; gt; you make an entry.
gt; gt; gt;
gt; gt; gt; Thanks
gt; gt; gt;
gt; gt; gt; SPL
gt; gt; gt;
gt; gt; gt;
gt; gt;
gt; gt;
gt;
gt;