locking cells based on results at runtime

A

aken

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
 
J

JPW

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)?
 
A

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
 
J

JPW

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 procedure:

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.
 
Top