MACROS: Protect a cell depending on the value of another cell
 

How can create a macro to protect a cell depending on the value of another
cell.
Ex: If A2<0 then I want A3 to be locked for editing.

Thanks for your help.

MACROS: Protect a cell depending on the value of another cell
 

frenchflo wrote:
How can create a macro to protect a cell depending on the value of another
cell.
Ex: If A2<0 then I want A3 to be locked for editing.

Thanks for your help.

If Range("A2").Value < 0 Then
ActiveSheet.Unprotect "your password here"
Range("A3").Locked = True
ActiveSheet.Protect "your password here"
End If

Ken Johnson

MACROS: Protect a cell depending on the value of another cell
 

Thanks for your quick answer.
But my worsheet needs to stay protected. Certain cells are already locked
and need to stay like that. So if I use your codes wont my worsheet be
unprotected if A20?

"Ken Johnson" wrote:

frenchflo wrote:
How can create a macro to protect a cell depending on the value of another
cell.
Ex: If A2<0 then I want A3 to be locked for editing.

Thanks for your help.

If Range("A2").Value < 0 Then
ActiveSheet.Unprotect "your password here"
Range("A3").Locked = True
ActiveSheet.Protect "your password here"
End If

Ken Johnson

MACROS: Protect a cell depending on the value of another cell
 

frenchflo wrote:
Thanks for your quick answer.
But my worsheet needs to stay protected. Certain cells are already locked
and need to stay like that. So if I use your codes wont my worsheet be
unprotected if A20?

"Ken Johnson" wrote:

frenchflo wrote:
How can create a macro to protect a cell depending on the value of another
cell.
Ex: If A2<0 then I want A3 to be locked for editing.

Thanks for your help.

If Range("A2").Value < 0 Then
ActiveSheet.Unprotect "your password here"
Range("A3").Locked = True
ActiveSheet.Protect "your password here"
End If

Hi,
If A20 then the code does nothing, it just skips to what ever code you
put after the End If statement, and your sheet remains protected, and
A3 remains unlocked if it was already unlocked.

Ken Johnson

MACROS: Protect a cell depending on the value of another cell
 

Hi,
If you are wanting the macro to run automatically you could try this
code in the worksheet code module.
Copy the code then right click the sheet tab and select "View code"
from the popup menu. Paste the code into the code module that then
appears. Then press Alt + F11 to get out of the VBA Editor.
Don't forget to edit the password.

Private Sub Worksheet_Calculate()
Me.Unprotect "your password"
If Range("A2").Value < 0 Then
Range("A3").Locked = True
Else: Range("A3").Locked = False
End If
Me.Protect "your password"
End Sub

Ken Johnson