Locking cell with Macro

[prajul89]

I have done this a lot of time before with Range.Locked = True statement, and it worked too.
But somehow the statement is not working for some cells.
I even checked the cell property where the lock check box was ticked. So my macro statement did the job. But still the user can change the cell value. There is something that I am missing. Please Help

 

[prajul89]

I even tried ?range("B5").locked = true in Immediate box which gave me the answer true. But still I could change the cell value.

 

[Andrew Poulsom]

You need to protect the worksheet for locking to take effect.

 

[ravi4ever]

You are only missing with the protection part, protect the worksheet with or without a password.

 

[prajul89]

Of course I have already protected it! only some cells are unlocked.
Is there some other property too, that I need to change??

 

[Andrew Poulsom]

You can't change a Range's Locked property if the worksheet is protected. So you need to unprotect the worksheet, change the cell's Locked property, then reprotect the worksheet.

 

[prajul89]

?cells(5,2).locked
TRUE
?cells(6,2).locked
FALSE
?cells(6,1).locked
TRUE

I tried this in Immediate Box.
Here I can edit cells(5,2) and cells(6,2). where as cells(6,1) is doing fine and locked.

so when cell(5,2) is locked then why is it allowing me to edit it.

 

[prajul89]

You can't change a Range's Locked property if the worksheet is protected. So you need to unprotect the worksheet, change the cell's Locked property, then reprotect the worksheet.

that is not the case here. Because if try to lock a cell when a sheet is protected then there would be a bug, I would surely know it.

 

[prajul89]

ActiveSheet.Unprotect "123"
ThisWorkbook.Sheets("Payment Entry").Range("B" & R & ":K" & R).Select
Selection.Locked = True
activesheet.protect "123"

Here R is row no.

I think there must me some other property that I am missing.

 

[Andrew Poulsom]

I think there must me some other property that I am missing.

In what way?