hi,

how a cell or a range of cells be locked based on another cells value? let
me explain

if cell(A1)'s value is less then 30 then cell(b2) or cells b2:b10 must
be locked. user must not be able to do anything then on these cells. othewise
it must be open for editing.

aken

What you need is a VBA procedure. Are you looking at EVERY value in column A
of this worksheet, which will then effect all cells in that row to the
right, or are you looking at just one cell (A1) which will effect just one
range (B2:B10)?

"aken" wrote in message
...
hi,

how a cell or a range of cells be locked based on another cells value?
let
me explain

if cell(A1)'s value is less then 30 then cell(b2) or cells b2:b10 must
be locked. user must not be able to do anything then on these cells.
othewise
it must be open for editing.

aken

well,

i just got to solve this by this method.

If (ActiveSheet.Cells(1,1)<30) then
ActiveSheet.Unprotect
Range("B1:B10").Locked = False
ActiveSheet.Protect
End If

i.e on one cell condition, whole of the range must be locked and this code
does just that. well, i have this new query...

when i run this code, though the cells/range get locked and data editing
prohibited, the cursor movement is possible.

how can i restrict even the cursor movement over it.

as seen in the protect sheet,

"select locked cells"; is usally unchecked manually which makes cursor
movement not possible. the same thing needs to be incorporated in the VBA.
as a test i tried this:

ActiveSheet.selectlockedcells = False

though there is no such thing, i want you to suggest what is the correct
one.

aken

"JPW" wrote:

What you need is a VBA procedure. Are you looking at EVERY value in column A
of this worksheet, which will then effect all cells in that row to the
right, or are you looking at just one cell (A1) which will effect just one
range (B2:B10)?

"aken" wrote in message
...
hi,

how a cell or a range of cells be locked based on another cells value?
let
me explain

if cell(A1)'s value is less then 30 then cell(b2) or cells b2:b10 must
be locked. user must not be able to do anything then on these cells.
othewise
it must be open for editing.

aken

Add the following line to your code:
ActiveSheet.EnableSelection = xlUnlockedCells

....and you will only be able to select unlocked cells. Your code appears to
be flawed in that it will never unlock your range, and you said that it
should be locked when under thirty, not when over. Perhaps you should try
something like this in your Worksheet_Change procedu

With ActiveSheet
If Target = .Range("A1") Then
.Range("A1").Locked = False
If .Range("A1").Value < 30 Then
.Range("B1:B10").Locked = True
If Not .ProtectContents Then .Protect Contents:=True,
UserInterfaceOnly:=True
Else
.Range("B1:B10").Locked = False
End If
End If
End With


This will also speed up your code execution because it won't run when ANY
cell is changed, but only when cell A1 is changed. Also, it will not affect
protection of other cells on your worksheet. If all of your cells are
protected by default, you could either add
.Unprotect
underneath the B1:B10 Locked = False line, or simply unprotect the ones you
want to keep editable.

"aken" wrote in message
...
well,

i just got to solve this by this method.

If (ActiveSheet.Cells(1,1)<30) then
ActiveSheet.Unprotect
Range("B1:B10").Locked = False
ActiveSheet.Protect
End If

i.e on one cell condition, whole of the range must be locked and this
code
does just that. well, i have this new query...

when i run this code, though the cells/range get locked and data editing
prohibited, the cursor movement is possible.

how can i restrict even the cursor movement over it.

as seen in the protect sheet,

"select locked cells"; is usally unchecked manually which makes cursor
movement not possible. the same thing needs to be incorporated in the
VBA.
as a test i tried this:

ActiveSheet.selectlockedcells = False

though there is no such thing, i want you to suggest what is the correct
one.

aken

"JPW" wrote:

What you need is a VBA procedure. Are you looking at EVERY value in
column A
of this worksheet, which will then effect all cells in that row to the
right, or are you looking at just one cell (A1) which will effect just
one
range (B2:B10)?

"aken" wrote in message
...
hi,

how a cell or a range of cells be locked based on another cells value?
let
me explain

if cell(A1)'s value is less then 30 then cell(b2) or cells b2:b10
must
be locked. user must not be able to do anything then on these cells.
othewise
it must be open for editing.

aken