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

No you can't use validation to lock a cell


--


Regards,


Peo Sjoblom


"MIke" wrote in message
...
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

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

On Wed, 24 Oct 2007 09:48:08 -0700, MIke wrote:

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

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

"Gord Dibben" wrote:

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

On Wed, 24 Oct 2007 09:48:08 -0700, MIke wrote:

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

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


On Thu, 25 Oct 2007 04:55:01 -0700, MIke wrote:

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

"Gord Dibben" wrote:

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

On Wed, 24 Oct 2007 09:48:08 -0700, MIke wrote:

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

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

"Gord Dibben" wrote:

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


On Thu, 25 Oct 2007 04:55:01 -0700, MIke wrote:

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

"Gord Dibben" wrote:

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

On Wed, 24 Oct 2007 09:48:08 -0700, MIke wrote:

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

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

On Thu, 1 Nov 2007 05:24:02 -0700, MIke wrote:

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

"Gord Dibben" wrote:

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


On Thu, 25 Oct 2007 04:55:01 -0700, MIke wrote:

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

"Gord Dibben" wrote:

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

On Wed, 24 Oct 2007 09:48:08 -0700, MIke wrote:

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