How to lock and unlock a range of cells based on certain cell's value?

[potato]

Hi everyone,

I am writing a macro to protect/lock a range of cells that are irrelevant to the user. However, I am very confused over the "protect" property and the "locked" property. Could some kind soul out there help to explain the differences to me??


Also, I wanted to write a macro to prevent users from entering anything inside a specific range of cells, depending on the different stage the users are currently in. I thought of using the password protection function in excel to do it but i am not sure how to achieve that using codes.

I have a specific cell to let users to indicate which stage they are in, so that I could depend on the value in the cell to lock those irrelevant range of cells (I don't want the users to enter anything for irrelevant fields) and unlock those relevant range of cells for users to enter inputs. Before I could use the specific cell to validate, I will need to lock all the fields before users have indicated which stage they are currently in. However, I do not know how to code it in a way that will achieve the results.

The following is something that I tried to come out with after researching online but not sure is it the correct way.

Please Login or Register  to view this content.

I have tried out the codes and it works fine. However, what if I want this piece of code to be triggered when the users first entered something on the worksheet? Not sure how to code it.

Please help! Thanks a million in advance.

Here is the sample file without the coding (explanation included):
Sample.xlsx

[pike]

Hi potato
You can use a worksheet event to trigger the cell locking.

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[potato]

Hi pike,

Do you mind explaining the difference between the two event handlers that you mentioned? Thanks

[pike]

hi potato
from the VBA help files
Worksheet.SelectionChange Event (Excel)
Occurs when the selection changes on a worksheet.
Worksheet.Change Event (Excel)
Occurs when cells on the worksheet are changed by the user or by an external link.

[potato]

Pike, thanks for your explanation! :D

[pike]

D can you attach a sample workbook?

[potato]

Hi pike, I have attached a sample file in the post as requested.

[pike]

Hi spud

with b2 unlocked

in the worksheet sheet module

Please Login or Register  to view this content.

[potato]

Hi Pike

Thanks for the help and sorry for the late reply. I had tried out the codes and it works. However, I will need to key in the password in order to change the value in cell B2 (I have unlocked cell B2 before protecting the sheet). Is it possible to do it in a way that I do not need to key in the password if I want to change the value in cell B2? Forgive me as I am still confuse over the impact on protecting of sheet and locking of cells.

[pike]

Hello
try a right click in the cell

Please Login or Register  to view this content.

[potato]

Thanks Pike!!

[pike]

your welcome spud