formula to lock cell?

M

MIke

Can you use a validation formula to lock a cell? If so could you give me an
example of how to write it?

Thanks in advance,
MIke
 
G

Gord Dibben

As Peo mentioned..........Formulas cannot lock cells.

DV can't lock cells.

Conditional formatting can't lock cells.

You would be best use VBA event code.

If you describe what you need locking and when, someone could supply some code.


Gord Dibben MS Excel MVP
 
M

MIke

Gord,

I have a formula in Column O7:O37. I need cells B7:N37 to lock when the
value from the formula exceeds a certain threshold.

Thanks for the help,
Mike
 
G

Gord Dibben

Assuming all cells are set to "unlocked" to begin with and sheet is password
protected

Private Sub Worksheet_Calculate()
Dim myCell As Range
On Error GoTo ws_exit:
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="justme"
For Each myCell In Range("O7:O37")

If myCell.Value > 8 Then
myCell.Offset(0, -13).Locked = True

End If
Next myCell
ActiveSheet.Protect Password:="justme"

ws_exit:
Application.EnableEvents = True
End Sub


Gord
 
M

MIke

Gord,

The code you gave me works but it only locks the cell 13 spots from the
target cell. Is there a way to lock all 13 cells to the left of the target?
Do you have any suggestions?

Thanks for your help,
Mike
 
G

Gord Dibben

Try this version Mike.

Private Sub Worksheet_Calculate()
Dim myCell As Range
On Error GoTo ws_exit:
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="justme"
For Each myCell In Range("O7:O37")
If myCell.Value > 8 Then
Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = True
Else
Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = False
End If
Next myCell
ActiveSheet.Protect Password:="justme"

ws_exit:
Application.EnableEvents = True
End Sub


Gord
 
G

Gord Dibben

You should be able to incorporate a second range into the same event.

You can't however, use two events of the same type in one sheet or you would get
a conflict.


Gord
 
M

MIke

Gord,

Is there a way to trigger the locking from a change in fill color? If I can
do this it will take care of both events. I have the cells conditionally
formatted to turn red if the thresholds are met. Thanks again for all of
your help.

Mike
 
G

Gord Dibben

Color change from CF will not trigger the calculate event.

The condition that caused the CF color change will trigger the event.

What is the "other range" and what do you need done when that range
re-calculates and meets threshholds?


Gord
 
M

MIke

The other range is Q7:Q37 and I need the same ranges of cells to lock (B:N).
Thanks,
Mike
 
G

Gord Dibben

That could conflict with O7:O37 threshholds, could it not?

Or do you mean if both O7 and Q7 were > 8 then lock B7:N7?

Or an if/or condition?


Gord
 
M

MIke

Gord,

They are two different thresholds. Is there a way I could do it with
macros. Thanks

Mike
 
G

Gord Dibben

Mike

You're a little tight-fisted with the details.

We've so far gotten columns B:N to lock based on a threshhold of >8 in O7:O37

Would Q7:Q37 override that? What's the threshhold in that range?

Are you wanting an IF AND condition?

Are you wanting an IF OR condition?


Gord
 
M

MIke

Gord,

Sorry. I am not tryiong to be tight fisted but I dont work with this stuff
alot.

I need column b:n to lock if the value in O7:O37 exceeds 8
Also, I need b:n to lock if the value in Q7:Q37 exceeds a different value "20"
I dont need it to overide the original range it is just another condition to
locking that group of cells so I think it is an IF OR condition. Let me know
if that makes sense to you. Thanks again for all the help.

Mike
 
Top