hi,

how can a cell be locked via VB scriprting in excel. to say, Cell A1 must be
locked if cell B1's value is less then (or any other conditions), say 35.
also, depending upon some condition, can the whole range of cells say
renge(A1:A40) also be locked???

as such the only way what am doing is: select the cell - format - cells -
protection tab - check-mark locked. this is manual setting, can this work be
done programatically??? that is what am on to.

i have tried by going to all the things like VB's programming codes that i
can go but just cant get the result.

any help will be truely appreciated.

taken

Cell or Cells (a range) can only be protected by applying protection to the
worksheet. If the cells in question are locked then protecting the
worksheet will protect the cells (and all other cells that are locked!).

Normally you would unlock the cell(s) you wish the user to change then apply
protection to the worksheet. Switching the cell locking and worksheet
protection on and off under programme control based on conditions or events
in the workbook or worksheet are all possible.

The following will unlock the range A1 to B3, allowing the user to edit the
cells after the worksheet is protected.

ActiveSheet.Unprotect
Range("A1:B3").Locked = False
ActiveSheet.Protect

The following reverese this situation

ActiveSheet.Unprotect
Range("A1:B3").Locked = True
ActiveSheet.Protect



--
Cheers
Nigel



"aken" wrote in message
...
hi,

how can a cell be locked via VB scriprting in excel. to say, Cell A1 must
be
locked if cell B1's value is less then (or any other conditions), say 35.
also, depending upon some condition, can the whole range of cells say
renge(A1:A40) also be locked???

as such the only way what am doing is: select the cell - format - cells -
protection tab - check-mark locked. this is manual setting, can this work
be
done programatically??? that is what am on to.

i have tried by going to all the things like VB's programming codes that i
can go but just cant get the result.

any help will be truely appreciated.

taken

hi,

so, thnks for the prompt Re. anyway, you have given the code but where do
i put the codes?

is there any way wherein i can put the codes in formulae or is it that VBA
components needs to be always present? here the solution is solved but my
problem still exists.

i would like to do this:

if cell A1 is less then say 33 or greater then 100, then cells B1:B45
should be locked automatically.

now the question is... where do i put the events code? can it be not made
as a conditional formatting type of thing where instead of locking, colors
are used depending on number values at real time. hope that the question is
clear from my end.

tnks

"Nigel" wrote:

Cell or Cells (a range) can only be protected by applying protection to the
worksheet. If the cells in question are locked then protecting the
worksheet will protect the cells (and all other cells that are locked!).

Normally you would unlock the cell(s) you wish the user to change then apply
protection to the worksheet. Switching the cell locking and worksheet
protection on and off under programme control based on conditions or events
in the workbook or worksheet are all possible.

The following will unlock the range A1 to B3, allowing the user to edit the
cells after the worksheet is protected.

ActiveSheet.Unprotect
Range("A1:B3").Locked = False
ActiveSheet.Protect

The following reverese this situation

ActiveSheet.Unprotect
Range("A1:B3").Locked = True
ActiveSheet.Protect



--
Cheers
Nigel



"aken" wrote in message
...
hi,

how can a cell be locked via VB scriprting in excel. to say, Cell A1 must
be
locked if cell B1's value is less then (or any other conditions), say 35.
also, depending upon some condition, can the whole range of cells say
renge(A1:A40) also be locked???

as such the only way what am doing is: select the cell - format - cells -
protection tab - check-mark locked. this is manual setting, can this work
be
done programatically??? that is what am on to.

i have tried by going to all the things like VB's programming codes that i
can go but just cant get the result.

any help will be truely appreciated.

taken

hi,

so lastly i could do the work partially. so...

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

"Nigel" wrote:

Cell or Cells (a range) can only be protected by applying protection to the
worksheet. If the cells in question are locked then protecting the
worksheet will protect the cells (and all other cells that are locked!).

Normally you would unlock the cell(s) you wish the user to change then apply
protection to the worksheet. Switching the cell locking and worksheet
protection on and off under programme control based on conditions or events
in the workbook or worksheet are all possible.

The following will unlock the range A1 to B3, allowing the user to edit the
cells after the worksheet is protected.

ActiveSheet.Unprotect
Range("A1:B3").Locked = False
ActiveSheet.Protect

The following reverese this situation

ActiveSheet.Unprotect
Range("A1:B3").Locked = True
ActiveSheet.Protect



--
Cheers
Nigel



"aken" wrote in message
...
hi,

how can a cell be locked via VB scriprting in excel. to say, Cell A1 must
be
locked if cell B1's value is less then (or any other conditions), say 35.
also, depending upon some condition, can the whole range of cells say
renge(A1:A40) also be locked???

as such the only way what am doing is: select the cell - format - cells -
protection tab - check-mark locked. this is manual setting, can this work
be
done programatically??? that is what am on to.

i have tried by going to all the things like VB's programming codes that i
can go but just cant get the result.

any help will be truely appreciated.

taken