Locking cell with Macro

prajul89

Active Member
Joined
Jul 9, 2011
Messages
404
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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

prajul89

Active Member
Joined
Jul 9, 2011
Messages
404
I even tried ?range("B5").locked = true in Immediate box which gave me the answer true. But still I could change the cell value.
 

ravi4ever

Active Member
Joined
Apr 13, 2010
Messages
316
You are only missing with the protection part, protect the worksheet with or without a password.
 

prajul89

Active Member
Joined
Jul 9, 2011
Messages
404

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Active Member
Joined
Jul 9, 2011
Messages
404

ADVERTISEMENT

?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

Active Member
Joined
Jul 9, 2011
Messages
404
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

Active Member
Joined
Jul 9, 2011
Messages
404
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,073
Messages
5,599,619
Members
414,325
Latest member
annamsreenu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top