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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
frenchflo said:
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.
Click to expand...

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

Ken Johnson
 
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 A2>0?
 
frenchflo said:
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 A2>0?
Click to expand...

Hi,
If A2>0 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
 
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
 
Back
Top